Solved

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

Posted on 2014-02-24
10
2,122 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

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 Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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 …

828 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