pmerobertson
10-17-2016, 12:58 PM
Hi, first post here (and very much learning VBA as I go) so please bear with me...
I've set up a macro to email the open document as an attachment to selected users, however any changes they make are lost on the email unless they save the document first - all the recipient of the email gets is a blank template. Not too much of an issue, but my colleagues are forgetful so I was hoping to add in a line to save the document first before it was emailed. Nothing I've tried seems to work...
Here's the code I've got so far, lifted straight from the rondebruin.nl website, with the addition of the first line ActiveWorkbook.Save -
Sub Sends()
'Working in Excel 2000-2016
'This example send the last saved version of the Activeworkbook
'For Tips see: [link removed so I can post...]
ActiveWorkbook.Save
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 = "an email address"
.CC = "more emails"
.BCC = ""
.Subject = "subject text"
.Body = "body message"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
On Error GoTo 0
End Sub
I've also tried creating a separate module with just ActiveWorkbook.Save which then calls the module above (minus the first line) but that doesn't work either...
Any help gratefully received!
Thanks,
Paul
I've set up a macro to email the open document as an attachment to selected users, however any changes they make are lost on the email unless they save the document first - all the recipient of the email gets is a blank template. Not too much of an issue, but my colleagues are forgetful so I was hoping to add in a line to save the document first before it was emailed. Nothing I've tried seems to work...
Here's the code I've got so far, lifted straight from the rondebruin.nl website, with the addition of the first line ActiveWorkbook.Save -
Sub Sends()
'Working in Excel 2000-2016
'This example send the last saved version of the Activeworkbook
'For Tips see: [link removed so I can post...]
ActiveWorkbook.Save
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 = "an email address"
.CC = "more emails"
.BCC = ""
.Subject = "subject text"
.Body = "body message"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
On Error GoTo 0
End Sub
I've also tried creating a separate module with just ActiveWorkbook.Save which then calls the module above (minus the first line) but that doesn't work either...
Any help gratefully received!
Thanks,
Paul