Automating Email VBA

[TimTim259]

So I've been tryin to automate email sending. I have a pdf I want to be emailed to every person in a column every time an email is added. I am struggling with adding the pdf I want to send with .attachment.add then the "location"

Here is what i have written so far.


Sub SendEmail()
'
' SendEmail Macro
'

'

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem

Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)

With OutlookMail
.BodyFormat = olFormatHTML
.Display
.HTMLBody = "This is a test"

.To = "testemail@gmail.com"
.CC = ""
.BCC = ""
.Subject = "TEST !"
.Attachments.Add = "Location"
.Send
End With
End Sub

 

[HaHoBe]

Hi,

code line

.Attachments.Add = "Location"

should read something like

  .Attachments.Add "C:\Daten\2022-09-26.png"

You cannot add the string "Location" but would need to give details for the path and the full name of the attachment.

Sub SendEmail_re()
'
' SendEmail Macro
'

'Late Binding, no Reference to Outlook needed

Dim objOlApp          As Object
Dim objOlMail         As Object
Dim strAttachment     As String

Set objOlApp = CreateObject("Outlook.Application")
Set objOlMail = objOlApp.CreateItem(0)
strAttachment = "C:\Daten\2022-09-26.png"     'change to suit

With objOlMail
  .BodyFormat = olFormatHTML
  .HTMLBody = "This is a test"
  .To = "testemail@gmail.com"
  .CC = ""
  .BCC = ""
  .Subject = "TEST !"
  .Attachments.Add strAttachment
  .Display
  .Send
End With

Set objOlMail = Nothing
Set objOlApp = Nothing

End Sub

You could alter the path to the attachment by referring to a cell in the worksheet.

Ciao,
Holger