V.Cell
09-25-2015, 07:43 AM
Hi guys,
I have a VBA code I am using for an excel spreadsheet that I am setting up in which several users will have their own copy. Once the information in the spreadsheet is completed, the user will click a button which will send an email to the intended recipient ( to be referenced in the code). However, I wanted to know if it is possible top modify this code so that the CC line automatically picks out the users email address (outlook), so that way when the user sends the email they also receive a copy as confirmation of the email being sent.
Please see the code below. Any help will be much appreciated.
Thanks!
Sub Mail_workbook_Outlook_1()
'Working in Excel 2000-2013
'This example send the last saved version of the Activeworkbook
'For Tips see:
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = ""
.CC = ""
.BCC = ""
.Subject = ""
.Body = ""
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
I have a VBA code I am using for an excel spreadsheet that I am setting up in which several users will have their own copy. Once the information in the spreadsheet is completed, the user will click a button which will send an email to the intended recipient ( to be referenced in the code). However, I wanted to know if it is possible top modify this code so that the CC line automatically picks out the users email address (outlook), so that way when the user sends the email they also receive a copy as confirmation of the email being sent.
Please see the code below. Any help will be much appreciated.
Thanks!
Sub Mail_workbook_Outlook_1()
'Working in Excel 2000-2013
'This example send the last saved version of the Activeworkbook
'For Tips see:
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = ""
.CC = ""
.BCC = ""
.Subject = ""
.Body = ""
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub