[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to send mass email with customized email body and subject by using a macro?

Posted on 2009-05-20
20
Medium Priority
?
456 Views
Last Modified: 2012-05-07
I am looking for a macro in outlook that would allow mass emailing with customized emails.  The mass emailer would use information from excel spreadsheet in the body of the email to send to multiple people.  Also, the body would use html file.
0
Comment
Question by:Amreska
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 2
20 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 24439463
Hi, Amreska.

I can help with that.  I'll need to know the layout of the Excel file.
0
 

Author Comment

by:Amreska
ID: 24441314
Hi Blue Devil Fan:

It will basically have multiple columns as shown in the file attached.

Thank You
Excel-Layout.xls
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24447989
Amreska

The sort of info we need is how will the custom email look and which fields map into the email for example which is the mail address?

Also will an email to every entry be required, or only specific ones and does evedryone get the same email or different.

Chris
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Amreska
ID: 24450331
The custom email will have fields that will be copied from the excel spreadsheet.  For example, In the body of the mail it will have "System Name".  There should be in the spreadsheet a column named "System Name".  The Custom Emails will copy the "System Name" from each row of the spreadsheet and will send to multiple people.

Each letter will will go to a specific email address.  The email address will be listed in the excel spreadsheet.  So that the macro will go to each row and send an email to the email address listed in each row.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24451350
The worksheet has columns OW Email      & AC Email which is used for the recipient(s)?

Are you ok to modify the body text to reflect your wishes if we create a method for the mail creation.

Would you like the email sent or displayed for preview and to click send yourself.

Chris
0
 

Author Comment

by:Amreska
ID: 24451427
Chris,

Yes that would be great.  One email preview of the first row is enough, by assuming that the first email will be similar to the rest of the emails that will be sent.  
0
 

Author Comment

by:Amreska
ID: 24451454
Chris,

Yes the OW and AC columns are the recepients email.

Thanks
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24451556
One email preview of the first row is enough, by assuming that the first email will be similar to the rest of the emails that will be sent.

Okay just have to knock the questions down ...

When sending emails automatically you need some add-ins or actions to overcome outlook security.  WHich version of excel and outlook are you using and which OS version?

Chris
0
 

Author Comment

by:Amreska
ID: 24453188
Chris,

Microsoft Excel 2003

Microsoft Outlook 2007

Microsoft Windows XP Professional Version 2002 Service Pack 2

Thanks
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24453663
I'm not sure what might be required for that combination but most likely MAPILab Advanced Outlook Security see http://www.mapilab.com/outlook/security/ it's a free COM add-in for Outlook.  I use it at work and with office 2003 but I suggest you wait and see if it is required.

Firstly therefore a test:

Use a blank worksheet and enter an email address in cell A1 then run the following sub, (paste it into an excel code module in the workbook ... which will demo how it will work all being well.  If it sends OK, (which I measure as no user interaction required ... since we are trying to automate the send) then simply let me know and the rest of this sequence is not required.

I would expect a security warning ... wait for the time out if it is triggered then allow.  Now install mapilab and when it is finished restart outlook and enable the add-in via prompts.

Once it seems to have stopped retry the sub and it should give you a MAPILAB warning if you select enable and always do this action then on  a subsequent re-run it should work seamlessly.  At this point we have the framework to proceed.

Chris
Sub mailTest()
Dim olkApp As Object
Dim mai As Object
 
    Set olkApp = CreateObject("outlook.application")
    Set mai = olkApp.createitem(0)
    mai.to = ActiveSheet.Range("a1")
    mai.Subject = "Delete Me"
    mai.send
End Sub

Open in new window

0
 

Author Comment

by:Amreska
ID: 24454421
Chris,

I get run time error 287
Application-defined or object-defined error

Why is that?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24454473
Ensure the worksheet with the email addy is selected then switch straight to the VBE and run the sub.

Any different, if not which line is highlighted with the error

Chris
0
 

Author Comment

by:Amreska
ID: 24454585
Chris,

It works but asks for security warning
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24454670
Did you install the MAPILAB dll, restart outlook and try a second time having accepted the MAPILAB warning with always do this action?

Chris
0
 

Author Comment

by:Amreska
ID: 24454764
Chris,

I installed Click Yes for now and it works.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24455137
OK then what you need now is to generate the emails.

See below, I have used display for evaluation, (just delete the opened emails till the format is correct) SO I suggest you have only 2 or three rows of data in the test file till you are happy with the mail format then change mai.display to mai.send

Chris
Sub mailTest()
Dim olkApp As Object
Dim mai As Object
Dim rw As Long
 
    Set olkApp = CreateObject("outlook.application")
    With ThisWorkbook.Worksheets("Sheet1")
        For rw = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
            Set mai = olkApp.createitem(0)
            mai.to = .Range("c" & rw) & "; " & .Range("c" & rw)
            mai.Subject = "Dunno"
            mai.body = "Dear Person" & vbCrLf & vbCrLf & _
            "In regard to " & .Range("a" & rw) & " Diddly ..." & vbCrLf & vbCrLf & _
            "Regards" & vbCrLf & "Me"
            mai.display
        Next
    End With
End Sub

Open in new window

0
 

Author Comment

by:Amreska
ID: 24455198
Chris,

Thanks.  How about HTML file?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24455926
Amreska,

Sorry I didn't get back to this question.  You're in great hands with Chris.  

Cheers!
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24456787
Use mai.htmlbody instead of .body

Chris
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 24457386
For example this is basically the same email

Chris
Sub mailTest()
Dim olkApp As Object
Dim mai As Object
Dim rw As Long
 
    Set olkApp = CreateObject("outlook.application")
    With ThisWorkbook.Worksheets("Sheet1")
        For rw = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
            Set mai = olkApp.createitem(0)
            mai.to = .Range("c" & rw) & "; " & .Range("c" & rw)
            mai.Subject = "Dunno"
            mai.htmlbody = "Dear Person" & "<br><br>" & _
            "In regard to " & .Range("a" & rw) & " Diddly ..." & "<br><br>" & _
            "Regards" & "<br>" & "Me"
            mai.display
        Next
    End With
End Sub

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question