View Single Post
  #1  
Old 05-10-2018, 12:48 PM
JuniorASA JuniorASA is offline
Member
 
Join Date: Oct 2004
Posts: 352
Default EXCEL Output Mass Email

I tried to write below code in EXCEL VBA to send the mass email. However, I cannot attach a picture in my email since I might not know what is the function / lines I should add to achieve this results (I.e. Append a picture in my email). Is there anyone can advise on any line / function so that I can achieve this purpose. Really appreciated.

Sub sendMassMail()

Dim i As Integer, j As Integer, direct_send As Boolean, htmlbody As String 'may have some implicit size limit here but not a problem here
direct_send = False
If EmailRecipients.Range("email_draftonly") = False Then
If InputBox("Please input ""Y"" to continue with automated mailing:", "Warning") = "Y" Then
direct_send = True
End If
End If
For i = 1 To EmailRecipients.Range("recipient_rowcount")

Dim TempFilePath As String
Set appOutlook = CreateObject("outlook.application")
Set Message = appOutlook.CreateItem(olMailItem)

htmlbody = ""
For j = 2 To HTML_Raw.Cells(1, 1).End(xlDown).Row
htmlbody = htmlbody & HTML_Raw.Cells(j, 1)
Next j

With Message
.Subject = EmailRecipients.Range("email_subject")
.htmlbody = "<html xmlns:o='urn:schemas-microsoft-com:office:office'" & _
"xmlns: x = 'urn:schemas-microsoft-com:office:excel'" & _
"xmlns='http://www.w3.org/TR/REC-html40'> " & _
"<head></head><body>" & _
HTML_Raw.Cells(1, 1) & _
EmailRecipients.Range("recipient_name").Offset(i, 0) & _
htmlbody & _
"</body></html>"
.Attachments.Add (ThisWorkbook.Path & "\" & Range("recipient_name").Offset(i, 2))
.Attachments.Add (ThisWorkbook.Path & "\" & Range("recipient_name").Offset(i, 3))

.To = EmailRecipients.Range("recipient_email").Offset(i, 0)
.Display
If direct_send Then
.Send
End If
End With
Next i

End Sub
Reply With Quote
 
Page generated in 0.10248 seconds with 9 queries