Actuarial Outpost
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Browse Open Actuarial Jobs

Life  Health  Casualty  Pension  Entry Level  All Jobs  Salaries

Thread Tools Search this Thread Display Modes
Old 05-10-2018, 12:48 PM
JuniorASA JuniorASA is offline
Join Date: Oct 2004
Posts: 353
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=''> " & _
"<head></head><body>" & _
HTML_Raw.Cells(1, 1) & _
EmailRecipients.Range("recipient_name").Offset(i, 0) & _
htmlbody & _
.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)
If direct_send Then
End If
End With
Next i

End Sub
Reply With Quote
Old 05-10-2018, 12:52 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 8,331
Blog Entries: 1

Ahh yes, spamming bum-shots to all your coworkers every 20 seconds, classic use of technology.

No idea off the top of my head, but have yet you tried pausing it in the middle and then adding a "watch" to Message, so you can look at the object?

Also, what happens to the attachments you are adding?

Or are you trying to add in line pictures??? Which might be trickier...
L’humour est la politesse du désespoir

Last edited by Sredni Vashtar; 05-10-2018 at 12:58 PM..
Reply With Quote
Old 05-25-2018, 09:56 AM
dumples dumples is offline
Join Date: Sep 2003
Posts: 1,251

I don't think this is going to be the cause of your problem, but

Set appOutlook = CreateObject("outlook.application")

shouldn't be part of the loop.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

All times are GMT -4. The time now is 02:52 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.28153 seconds with 9 queries