Solved

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

Posted on 2014-02-24
10
2,230 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 51

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 51

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 51

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 81

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

687 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