Dear Friends,

I have spreadsheet where each row follows this pattern: Name, dollar
amount, number_x, number_y, e-mail address. I have a variable that
holds the exact number of names on this list: RngRow and can be used in
a for/next cycle if needed.

I would like some help creating a macro that would do the following:

1) "Grab" the Name, dollar amount and e-mail address found in row 1.
These would be found in columns, A, B & E.
2) Open an e-mail in the default pop3 e-mail software of the user,
using the e-mail address "grabbed" from the current row.
3) Place the Name and dollar amount in the following e-mail message:
Dear (Name),
This is a quick e-mail to let you know that you have been sent the
amount of (amount).
4) Save the current e-mail (CTRL+S) so it can be viewed before
manually sending.
5) Goto step 1 above repeating this process until all of the
individuals on the list have a "personalized" e-mail created for them.

Just for your information I use the Eudora Pro e-mail program and have
never had a problem getting it to open when I click "Mailto" links.

Thank you very much!

Mike


---
Message posted from http://www.ExcelForum.com/

Mike

As you are most probably aware, manipulating the email client is going to be
the most difficult part. Below is some code to this point, for getting the
detail into variables.

There are several ways of sending email from Excel, depending on version,
but none will successfully do all you want. (For example you could use the
SendMail Method, but this send a workbook to the recipient and has no
message body parameter).

You might also consider automating a mail merge from word, using Excel as
the source. (This is a little difficult when overcoming Outlook 2002 and up
security, but may be another solution.

For what it's worth. Here's the code for collecting variables and iterating
the range (Presumes start in 2nd row. (one for headers) and UK currency
format)

Sub EmailAll()
Dim lLastRow As Long
Dim myCell As Range
Dim sName As String
Dim sEmail As String
Dim cAmount As Currency
Dim sEmailMessage As String

lLastRow = Range("A65536").End(xlUp).Row

For Each myCell In Range("A2:A" & lLastRow)
With myCell
sName = .Value
cAmount = .Offset(0, 1).Value
sEmail = .Offset(0, 4).Value
End With

sEmailMessage = "Dear " & sName & "," & Chr(13) & Chr(13) & _
"This is a quick e-mail to let you know that " & _
"you have been sent the amount of " & Format(cAmount, "£#,##0.00") & "."

Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"mjwillyone" wrote in message
...
Dear Friends,

I have spreadsheet where each row follows this pattern: Name, dollar
amount, number_x, number_y, e-mail address. I have a variable that
holds the exact number of names on this list: RngRow and can be used in
a for/next cycle if needed.

I would like some help creating a macro that would do the following:

1) "Grab" the Name, dollar amount and e-mail address found in row 1.
These would be found in columns, A, B & E.
2) Open an e-mail in the default pop3 e-mail software of the user,
using the e-mail address "grabbed" from the current row.
3) Place the Name and dollar amount in the following e-mail message:
Dear (Name),
This is a quick e-mail to let you know that you have been sent the
amount of (amount).
4) Save the current e-mail (CTRL+S) so it can be viewed before
manually sending.
5) Goto step 1 above repeating this process until all of the
individuals on the list have a "personalized" e-mail created for them.

Just for your information I use the Eudora Pro e-mail program and have
never had a problem getting it to open when I click "Mailto" links.

Thank you very much!

Mike


---
Message posted from http://www.ExcelForum.com/

Nick,

Thanks for the help. I must say that this is the best forum I have
used for Excel programming thus far!

Mike


---
Message posted from http://www.ExcelForum.com/

Mike

Whilst we do not mind the portal, please be aware we are not answering
questions on the forum. We are answering you question that has arrived on
the Microsoft public newsgroups. ExcelForum is simply a portal (Door) that
lays your questions on here.

Glad to help anyhow!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"mjwillyone" wrote in message
...
Nick,

Thanks for the help. I must say that this is the best forum I have
used for Excel programming thus far!

Mike


---
Message posted from http://www.ExcelForum.com/