Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells values included in personalized e-mails
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells values included in personalized e-mails
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells values included in personalized e-mails
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells values included in personalized e-mails
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding cells being included in a chart | Charts and Charting in Excel | |||
Cells not included in formulas | Excel Discussion (Misc queries) | |||
pivot table with selection values not included in the base data | Charts and Charting in Excel | |||
How to display the "values" included in a formula vs. the cells? | Excel Worksheet Functions | |||
How do I "turn off" cells so they are not included in autosum | Excel Worksheet Functions |