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


Reply
 
Thread Tools Search this Thread Display Modes
  #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
  #2  
Old 05-10-2018, 12:52 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 6,308
Blog Entries: 1
Default

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...
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.

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

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
Reply

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 06:26 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, 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.16978 seconds with 11 queries