PDA

View Full Version : [SOLVED] Data lost when sending file as attachment



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

pmerobertson
10-18-2016, 02:43 PM
Not to worry, I found a workaround that I didn't expect to work on the secure, thin client network I'm on...