I'm trying to set up a file that will attach itself to an e-mail, with recipients, subject and body already populated.
N.B. Our system requires users to assign a confidentiality before sending, which cannot be automated.
So the idea is that the user will click a button, the e-mail will appear ready for; review, confidentiality assigning then sending.
Did find previous post, but nothing happens (below).
Code: Select all
Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim wsh As Worksheet
Dim r As Long
Dim m As Long
' Save workbook
ActiveWorkbook.Save
Set wsh = Worksheets("Recipients")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
m = wsh.Range("B2").End(xlDown).Row
For r = 2 To m
.Recipients.Add wsh.Range("A" & r).Value
Next r
.Subject = "TESTER" & Format(Now(), "dd-mm-yy")
.Body = "Hi," & vbCr & vbLf & "Please see the attached Dock Fail"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub