Solved

How can you do a mail shot from outlook using data in Excel (Office 2007)

Posted on 2014-02-24
10
2,171 Views
Last Modified: 2014-03-15
Hi,

We've got a list of contacts we need to send an update too.

The contacts are listed in Excel.  We use Outlook 2007 (with a few 2010's).

How can we do this?

I'd rather not use word as we're trying to keep it simple.

THanks
0
Comment
Question by:jmsjms
[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
  • 6
  • 3
10 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39882212
Hi,

pls try

Sub Macro()

Set OL = CreateObject("Outlook.Application")

For Each xlRecipient In Range("A1:A5")

    Set MailSendItem = OL.CreateItem(olMailItem)
    With MailSendItem
        .Subject = "Your Subject"
        .Body = "Your BodyText"
        .To = xlRecipient
        .Display
        '.Send
    End With
Next
Set OL = Nothing

End Sub

Open in new window

Regards
0
 

Author Comment

by:jmsjms
ID: 39882235
Thanks but the email shot needs to be formatted with the Recipients name in.

Dear [Name]

Blah, blah, blah...

etc.

So we need to put in fields and customisation.

Is there anyway to do this in Outlook?

Cheers
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39882251
see example if the name is in the C Column you could do it like that

Sub Macro()

Set OL = CreateObject("Outlook.Application")

For Each xlRecipient In Range("A1:A25")

    Set MailSendItem = OL.CreateItem(olMailItem)
    With MailSendItem
        .Subject = "Your Subject"
        .Body = "Dear " & xlRecipient.Offset(0,2) & "," & vbCrLf & "Rest of Your BodyText"
        .To = xlRecipient
        .Display
        '.Send
    End With
Next
Set OL = Nothing

End Sub

Open in new window

0
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!

 

Author Comment

by:jmsjms
ID: 39882257
Hi Rgonzo,

Thanks for the info.  This is really good for tech stuff, but I'm trying to source something for the marketing department so it needs to to WYSIWYG and for them to put pics in, formatted text etc, rather than a script process.  They would just pull their collective hair out using script.

Sorry should have noted this in the question. :-)
0
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 total points
ID: 39882273
In that case I would recommend to make a mailmerge with word with the XL file as data source

refer to

http://office.microsoft.com/en-us/word-help/use-mail-merge-to-send-personalized-e-mail-messages-to-your-e-mail-address-list-HA010109162.aspx
Regards
0
 

Author Comment

by:jmsjms
ID: 39882279
Does that output a word document thats emailed or a native HTML email?
0
 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 39884146
It will be in HTML with the normal word excess garbage
0
 

Author Comment

by:jmsjms
ID: 39884980
So all in all, there's no simple way to create a formatted Customised Eshot just with MS Office, unless you use Word?
0
 

Accepted Solution

by:
jmsjms earned 0 total points
ID: 39917385
In the end I found this tool that works with the WOrd mail merge.  It's probably just a GUI for a script but it's great for office users.

http://www.mapilab.com/outlook/mail_merge/
0
 

Author Closing Comment

by:jmsjms
ID: 39931081
I've marked my comment as a solution as the tool I found allowed us to send mail merges with attachments and made it easy for staff.

All points to Rgonzo1971 as he confirmed that word has to be used as part of the process for a mail merge (if you dont use a mail merge system)
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

749 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