How do I take cell data and automatically put it in an email?

[Guest]

I have an Excel spreadsheet of donations with a YTD total for each donors. I
want to send each donor an email showing this YTD total. Is there a way to
automatically prepare an email for about 100 different donors showing their
individual YTD donations as listed on my Excel spreadsheet?

 

[Richard Buttrey]

On Fri, 24 Mar 2006 08:55:02 -0800, carmel loughman <carmel

I have an Excel spreadsheet of donations with a YTD total for each donors. I
want to send each donor an email showing this YTD total. Is there a way to
automatically prepare an email for about 100 different donors showing their
individual YTD donations as listed on my Excel spreadsheet?

The following will work if you're using Outlook

With the email names of your donors in A2:Axx, and their total YTD
donation in B2:Bxx, and A1 & B1 being column headings, name A1 with
the name "email_list"

Then put the following macro in the VBA environment and run it. Make
sure that in the VBA environment, in the Tools References popup list,
that the Microsoft Outlook xx Object Library is ticked.
The Macro will populate C2:Cxx with the words Sent or Not Sent. If the
names are references to short names in your address book as opposed to
(e-mail address removed) names and the name doesn't exist, 'Not sent' will appear in
Col C

The only frustration is that Microsoft has decided that an automatic
process like this shouldn't be allowed to run in case it's a malicious
program which is distributing itself. You therefore have to answer
'Yes' every time a new email is created.

To get round this I use a small memory resident program called
'ClickYes.exe', which automatically clicks the 'yes' button every
time.


Sub SendeMail()
Dim stTxtto As String, stTxtBody As String
Dim iEmailNo As Integer 'Number of Email Names in list to be sent
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim olMyRecipient 'Email Name
Dim x As Integer

Set olApp = New Outlook.Application
iEmailNo = Range("email_list").CurrentRegion.Rows.Count - 1

For x = 1 To iEmailNo
Set olMail = olApp.CreateItem(olMailItem)
stTxtto = Range("email_list").Offset(x, 0)
Set olMyRecipient = olMail.Recipients.Add(stTxtto)
If olMyRecipient.Resolve Then

With olMail
.To = stTxtto
.Subject = "Year to Date Totals"
.Body = "Your Year to date Total is: " &
Range("email_list").Offset(x, 1)
End With

olMail.Send
Range("email_list").Offset(x, 2) = "Sent"
Else
Range("email_list").Offset(x, 2) = "Not Sent"
End If
Next
End Sub

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

Carmel --

I know you can use Excel to support the MailMerge utility in Word, so I
suspect you can do it in Outlook as well. I'd ask your question over there.
While waiting for an answer, I'd make sure that one of the columns has valid
e-mail addresses.

Sorry I can't be more helpful.

Peter