If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
You made it easy for even ME to do and I think I did it perfect (and I didn't
have a clue as to what I was doing). The only problem is that it is sending to only the 1st e-mail address. The piece of e-mail has all the addresses, but Outlook is only sending to the frst one. Personally, I'd rather have ONE address per piece of e-mail (so that a column of 20 addresses will send 20 pieces of e-mail, each with only one e-mail address. Thanks. "Ken Wright" wrote: LOL - it looks a lot scarier than it really is - try this for a step by step guide using Ron's code. Hit ALT+F11 and this will open the VBE (Visual Basic Editor) Top left you will hopefully see an explorer style pane. Within this pane you need to search for your workbook's name, and when you find it you may need to click on the + to expand it. Within that you should see the following:- VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) ThisWorkbook If you have named your sheets then those names will appear in the brackets above as opposed to what you see at the moment in my note. Right click on the where it says VBAProject(Your_Filename) and choose 'Insert Module' and it will now look like this VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) ThisWorkbook Modules Module1 Double click the Module1 bit and then paste in Ron's code starting at the Sub Mail_workbook_Outlook() bit and finishing at the End Sub bit. Sub Mail_workbook_Outlook() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim strto As String For Each cell In ThisWorkbook.Sheets("Sheet1") _ .Columns("C").Cells.SpecialCells(xlCellTypeConstan ts) If cell.Value Like "*@*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = strto .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ("C:\test.doc") .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub The ranges in Ron's code will likely need changing to suit your data, eg in the line that says For Each cell In ThisWorkbook.Sheets("Sheet1") you will need to either change the Sheet1 to the name of your sheet or vice versa. Also, Ron's code assumes the addresses are in Col C on that sheet, hence the line that says .Columns("C").Cells.SpecialCells(xlCellTypeConstan ts) so if it's not Col C then just change the C to whatever it actually is in your sheet. Then hit File / Close and return to Microsoft Excel and save the file. Now just do Tools / Macro / Macros / Mail_workbook You can stop at that point, but if for any reason you then want to get rid of the macro, then simply do the following:- Hit ALT+F11 and this will open the VBE (Visual Basic Editor) Top left you will hopefully see an explorer style pane. Within this pane you need to search for your workbook's name, and when you find it you may need to click on the + to expand it. Within that you should see the following:- VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) etc.......................... ThisWorkbook Modules Module1 Right click on the Module1 and select remove. When prompted with a question re exporting, just hit no. Then hit File / Close and return to Microsoft Excel and save the file. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- snip |
#12
|
|||
|
|||
Each of my three e-mail addresses is a different account with a different
name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? "Ron de Bruin" wrote: Hi MrMan&Fam it is only sending e-mail to the 1st address That's because they all yours and you will receive them. See this example on my website http://www.rondebruin.nl/mail/folder2/files.htm It is bed time for me now ( 23:31) but is if you need help post back and I will help you tomorrow after work. -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... I can NOT believe it! Without knowing what the hell I was doing, I followed your instructions and did (I believe) everything right. As a test, I created a NEW column "C" with only 3 e-mail addresses (all mine), I edited the attachment name, the "CC", all went well. I even got a message saying that "the computer is sending mail. This could be a virus..." BUT, it is only sending e-mail to the 1st address. It's listing all the other addresses in the "TO" section but it is not sending to the other addresses. Only the 1st one. Also, can I have it put only ONE ""TO" address and make a separate e-mail for each address (not putting them all on the "TO" section). Thanks. Great job! "Ron de Bruin" wrote: Here we Go Only working if you use Outlook this example Open a new workbook InsertModule from the menu bar Paste the sub in there Change the path/filename to the word Doc in the code Alt-Q to go back to Excel Now in the C column of "Sheet1" fill in your addresses Save the file If you do Alt-F8 you get a list of your macro's Select "Mail_workbook_Outlook" and press Run You can also use a cell in "Sheet1" with the path/filename to the word Doc if you want.(post back if you need help with that) For testing you can change .Send to .display in the code You can see how it look like and press the Send button manual then -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Thanks so much for the prompt response. Your work looks brilliant but it's wasted on me. I don't know anything about programming or programming in Excel. I don't even know what to do with your program. However, if this is what it takes, I guess there's no easy way. Thanks, anyway. "Ron de Bruin" wrote: Hi Try this one with the e-mail addresses in "Sheet1" column C Visit also my site for more examples Sub Mail_workbook_Outlook() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim strto As String For Each cell In ThisWorkbook.Sheets("Sheet1") _ .Columns("C").Cells.SpecialCells(xlCellTypeConstan ts) If cell.Value Like "*@*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = strto .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ("C:\test.doc") .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Say I have an Excel spreadsheet with one column of e-mail addresses. I want to send the same piece of e-mail (a WORD file) to each address. Is there an easy way? |
#13
|
|||
|
|||
Hi
Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? It is working, disable one account and send the mail,you will get one mail. Enabled the other account and you will see you will receive a mail I live in the Netherlands in Europe -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? "Ron de Bruin" wrote: Hi MrMan&Fam it is only sending e-mail to the 1st address That's because they all yours and you will receive them. See this example on my website http://www.rondebruin.nl/mail/folder2/files.htm It is bed time for me now ( 23:31) but is if you need help post back and I will help you tomorrow after work. -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... I can NOT believe it! Without knowing what the hell I was doing, I followed your instructions and did (I believe) everything right. As a test, I created a NEW column "C" with only 3 e-mail addresses (all mine), I edited the attachment name, the "CC", all went well. I even got a message saying that "the computer is sending mail. This could be a virus..." BUT, it is only sending e-mail to the 1st address. It's listing all the other addresses in the "TO" section but it is not sending to the other addresses. Only the 1st one. Also, can I have it put only ONE ""TO" address and make a separate e-mail for each address (not putting them all on the "TO" section). Thanks. Great job! "Ron de Bruin" wrote: Here we Go Only working if you use Outlook this example Open a new workbook InsertModule from the menu bar Paste the sub in there Change the path/filename to the word Doc in the code Alt-Q to go back to Excel Now in the C column of "Sheet1" fill in your addresses Save the file If you do Alt-F8 you get a list of your macro's Select "Mail_workbook_Outlook" and press Run You can also use a cell in "Sheet1" with the path/filename to the word Doc if you want.(post back if you need help with that) For testing you can change .Send to .display in the code You can see how it look like and press the Send button manual then -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Thanks so much for the prompt response. Your work looks brilliant but it's wasted on me. I don't know anything about programming or programming in Excel. I don't even know what to do with your program. However, if this is what it takes, I guess there's no easy way. Thanks, anyway. "Ron de Bruin" wrote: Hi Try this one with the e-mail addresses in "Sheet1" column C Visit also my site for more examples Sub Mail_workbook_Outlook() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim strto As String For Each cell In ThisWorkbook.Sheets("Sheet1") _ .Columns("C").Cells.SpecialCells(xlCellTypeConstan ts) If cell.Value Like "*@*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = strto .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ("C:\test.doc") .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Say I have an Excel spreadsheet with one column of e-mail addresses. I want to send the same piece of e-mail (a WORD file) to each address. Is there an easy way? |
#14
|
|||
|
|||
You must be right. I found the 2nd of the 3 pieces of mail I sent myself
later that evening. Later THIS evening I want to run the macro on a subset of the REAL list. What about my question regarding sending the same document to only ONE address at a time instead of the entire column? Among other things, some e-mail programs look at mail with multiple "TO" addresses as SPAM but I also don't want any of the recipients to know how many I'm sending out. "Ron de Bruin" wrote: Hi Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? It is working, disable one account and send the mail,you will get one mail. Enabled the other account and you will see you will receive a mail I live in the Netherlands in Europe -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? "Ron de Bruin" wrote: Hi MrMan&Fam it is only sending e-mail to the 1st address That's because they all yours and you will receive them. See this example on my website http://www.rondebruin.nl/mail/folder2/files.htm It is bed time for me now ( 23:31) but is if you need help post back and I will help you tomorrow after work. -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... I can NOT believe it! Without knowing what the hell I was doing, I followed your instructions and did (I believe) everything right. As a test, I created a NEW column "C" with only 3 e-mail addresses (all mine), I edited the attachment name, the "CC", all went well. I even got a message saying that "the computer is sending mail. This could be a virus..." BUT, it is only sending e-mail to the 1st address. It's listing all the other addresses in the "TO" section but it is not sending to the other addresses. Only the 1st one. Also, can I have it put only ONE ""TO" address and make a separate e-mail for each address (not putting them all on the "TO" section). Thanks. Great job! "Ron de Bruin" wrote: Here we Go Only working if you use Outlook this example Open a new workbook InsertModule from the menu bar Paste the sub in there Change the path/filename to the word Doc in the code Alt-Q to go back to Excel Now in the C column of "Sheet1" fill in your addresses Save the file If you do Alt-F8 you get a list of your macro's Select "Mail_workbook_Outlook" and press Run You can also use a cell in "Sheet1" with the path/filename to the word Doc if you want.(post back if you need help with that) For testing you can change .Send to .display in the code You can see how it look like and press the Send button manual then -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Thanks so much for the prompt response. Your work looks brilliant but it's wasted on me. I don't know anything about programming or programming in Excel. I don't even know what to do with your program. However, if this is what it takes, I guess there's no easy way. Thanks, anyway. "Ron de Bruin" wrote: Hi Try this one with the e-mail addresses in "Sheet1" column C Visit also my site for more examples Sub Mail_workbook_Outlook() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim strto As String For Each cell In ThisWorkbook.Sheets("Sheet1") _ .Columns("C").Cells.SpecialCells(xlCellTypeConstan ts) If cell.Value Like "*@*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = strto .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ("C:\test.doc") .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Say I have an Excel spreadsheet with one column of e-mail addresses. I want to send the same piece of e-mail (a WORD file) to each address. Is there an easy way? |
#15
|
|||
|
|||
See the link I posted or use
To for your own address and BCC for all other addresses http://www.rondebruin.nl/mail/folder2/files.htm -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... You must be right. I found the 2nd of the 3 pieces of mail I sent myself later that evening. Later THIS evening I want to run the macro on a subset of the REAL list. What about my question regarding sending the same document to only ONE address at a time instead of the entire column? Among other things, some e-mail programs look at mail with multiple "TO" addresses as SPAM but I also don't want any of the recipients to know how many I'm sending out. "Ron de Bruin" wrote: Hi Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? It is working, disable one account and send the mail,you will get one mail. Enabled the other account and you will see you will receive a mail I live in the Netherlands in Europe -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? "Ron de Bruin" wrote: Hi MrMan&Fam it is only sending e-mail to the 1st address That's because they all yours and you will receive them. See this example on my website http://www.rondebruin.nl/mail/folder2/files.htm It is bed time for me now ( 23:31) but is if you need help post back and I will help you tomorrow after work. -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... I can NOT believe it! Without knowing what the hell I was doing, I followed your instructions and did (I believe) everything right. As a test, I created a NEW column "C" with only 3 e-mail addresses (all mine), I edited the attachment name, the "CC", all went well. I even got a message saying that "the computer is sending mail. This could be a virus..." BUT, it is only sending e-mail to the 1st address. It's listing all the other addresses in the "TO" section but it is not sending to the other addresses. Only the 1st one. Also, can I have it put only ONE ""TO" address and make a separate e-mail for each address (not putting them all on the "TO" section). Thanks. Great job! "Ron de Bruin" wrote: Here we Go Only working if you use Outlook this example Open a new workbook InsertModule from the menu bar Paste the sub in there Change the path/filename to the word Doc in the code Alt-Q to go back to Excel Now in the C column of "Sheet1" fill in your addresses Save the file If you do Alt-F8 you get a list of your macro's Select "Mail_workbook_Outlook" and press Run You can also use a cell in "Sheet1" with the path/filename to the word Doc if you want.(post back if you need help with that) For testing you can change .Send to .display in the code You can see how it look like and press the Send button manual then -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Thanks so much for the prompt response. Your work looks brilliant but it's wasted on me. I don't know anything about programming or programming in Excel. I don't even know what to do with your program. However, if this is what it takes, I guess there's no easy way. Thanks, anyway. "Ron de Bruin" wrote: Hi Try this one with the e-mail addresses in "Sheet1" column C Visit also my site for more examples Sub Mail_workbook_Outlook() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim strto As String For Each cell In ThisWorkbook.Sheets("Sheet1") _ .Columns("C").Cells.SpecialCells(xlCellTypeConstan ts) If cell.Value Like "*@*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = strto .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ("C:\test.doc") .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Say I have an Excel spreadsheet with one column of e-mail addresses. I want to send the same piece of e-mail (a WORD file) to each address. Is there an easy way? |
#16
|
|||
|
|||
Following the procedure I used with the 1st file you sent, I did the same
with the second file (and I made a special spreadsheet with 3 columns. One for name...) yet I can't get the macro to show up when I click TOOLS-MACRO-MACRO in my special spreadsheet) so I can't run it. Why? "Ron de Bruin" wrote: See the link I posted or use To for your own address and BCC for all other addresses http://www.rondebruin.nl/mail/folder2/files.htm -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... You must be right. I found the 2nd of the 3 pieces of mail I sent myself later that evening. Later THIS evening I want to run the macro on a subset of the REAL list. What about my question regarding sending the same document to only ONE address at a time instead of the entire column? Among other things, some e-mail programs look at mail with multiple "TO" addresses as SPAM but I also don't want any of the recipients to know how many I'm sending out. "Ron de Bruin" wrote: Hi Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? It is working, disable one account and send the mail,you will get one mail. Enabled the other account and you will see you will receive a mail I live in the Netherlands in Europe -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? "Ron de Bruin" wrote: Hi MrMan&Fam it is only sending e-mail to the 1st address That's because they all yours and you will receive them. See this example on my website http://www.rondebruin.nl/mail/folder2/files.htm It is bed time for me now ( 23:31) but is if you need help post back and I will help you tomorrow after work. -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... I can NOT believe it! Without knowing what the hell I was doing, I followed your instructions and did (I believe) everything right. As a test, I created a NEW column "C" with only 3 e-mail addresses (all mine), I edited the attachment name, the "CC", all went well. I even got a message saying that "the computer is sending mail. This could be a virus..." BUT, it is only sending e-mail to the 1st address. It's listing all the other addresses in the "TO" section but it is not sending to the other addresses. Only the 1st one. Also, can I have it put only ONE ""TO" address and make a separate e-mail for each address (not putting them all on the "TO" section). Thanks. Great job! "Ron de Bruin" wrote: Here we Go Only working if you use Outlook this example Open a new workbook InsertModule from the menu bar Paste the sub in there Change the path/filename to the word Doc in the code Alt-Q to go back to Excel Now in the C column of "Sheet1" fill in your addresses Save the file If you do Alt-F8 you get a list of your macro's Select "Mail_workbook_Outlook" and press Run You can also use a cell in "Sheet1" with the path/filename to the word Doc if you want.(post back if you need help with that) For testing you can change .Send to .display in the code You can see how it look like and press the Send button manual then -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Thanks so much for the prompt response. Your work looks brilliant but it's wasted on me. I don't know anything about programming or programming in Excel. I don't even know what to do with your program. However, if this is what it takes, I guess there's no easy way. Thanks, anyway. "Ron de Bruin" wrote: Hi Try this one with the e-mail addresses in "Sheet1" column C Visit also my site for more examples Sub Mail_workbook_Outlook() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim strto As String For Each cell In ThisWorkbook.Sheets("Sheet1") _ .Columns("C").Cells.SpecialCells(xlCellTypeConstan ts) If cell.Value Like "*@*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = strto .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ("C:\test.doc") .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Say I have an Excel spreadsheet with one column of e-mail addresses. I want to send the same piece of e-mail (a WORD file) to each address. Is there an easy way? |
#17
|
|||
|
|||
Whenever I close up the spreadsheet with the macro I made with your help,
when I go back to the spreadsheet again the macro is gone. I have to cut and paste all over again. Why? "Ron de Bruin" wrote: See the link I posted or use To for your own address and BCC for all other addresses http://www.rondebruin.nl/mail/folder2/files.htm -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... You must be right. I found the 2nd of the 3 pieces of mail I sent myself later that evening. Later THIS evening I want to run the macro on a subset of the REAL list. What about my question regarding sending the same document to only ONE address at a time instead of the entire column? Among other things, some e-mail programs look at mail with multiple "TO" addresses as SPAM but I also don't want any of the recipients to know how many I'm sending out. "Ron de Bruin" wrote: Hi Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? It is working, disable one account and send the mail,you will get one mail. Enabled the other account and you will see you will receive a mail I live in the Netherlands in Europe -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? "Ron de Bruin" wrote: Hi MrMan&Fam it is only sending e-mail to the 1st address That's because they all yours and you will receive them. See this example on my website http://www.rondebruin.nl/mail/folder2/files.htm It is bed time for me now ( 23:31) but is if you need help post back and I will help you tomorrow after work. -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... I can NOT believe it! Without knowing what the hell I was doing, I followed your instructions and did (I believe) everything right. As a test, I created a NEW column "C" with only 3 e-mail addresses (all mine), I edited the attachment name, the "CC", all went well. I even got a message saying that "the computer is sending mail. This could be a virus..." BUT, it is only sending e-mail to the 1st address. It's listing all the other addresses in the "TO" section but it is not sending to the other addresses. Only the 1st one. Also, can I have it put only ONE ""TO" address and make a separate e-mail for each address (not putting them all on the "TO" section). Thanks. Great job! "Ron de Bruin" wrote: Here we Go Only working if you use Outlook this example Open a new workbook InsertModule from the menu bar Paste the sub in there Change the path/filename to the word Doc in the code Alt-Q to go back to Excel Now in the C column of "Sheet1" fill in your addresses Save the file If you do Alt-F8 you get a list of your macro's Select "Mail_workbook_Outlook" and press Run You can also use a cell in "Sheet1" with the path/filename to the word Doc if you want.(post back if you need help with that) For testing you can change .Send to .display in the code You can see how it look like and press the Send button manual then -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Thanks so much for the prompt response. Your work looks brilliant but it's wasted on me. I don't know anything about programming or programming in Excel. I don't even know what to do with your program. However, if this is what it takes, I guess there's no easy way. Thanks, anyway. "Ron de Bruin" wrote: Hi Try this one with the e-mail addresses in "Sheet1" column C Visit also my site for more examples Sub Mail_workbook_Outlook() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim strto As String For Each cell In ThisWorkbook.Sheets("Sheet1") _ .Columns("C").Cells.SpecialCells(xlCellTypeConstan ts) If cell.Value Like "*@*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = strto .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ("C:\test.doc") .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Say I have an Excel spreadsheet with one column of e-mail addresses. I want to send the same piece of e-mail (a WORD file) to each address. Is there an easy way? |
#19
|
|||
|
|||
This might sound stupid to you but in fact, I did NOT. The spreadsheet has
138 e-mail addresses and I did not want to send to all at once. So, I would clear contents from a group of e-mail address cells leaving only the group I wanted to mail to. Once I ran the macro, I closed the file DELIBERATELY without saving. This way, when I opened it again, ALL the addresses would be back on. Then I would clear contents of another group of cells in order to send to a different set of addresses, etc. SO, unless you save the spreadsheet, the macro doesn't get saved? Thanks. "Don Guillett" wrote: You did save the workbook afterwards... -- Don Guillett SalesAid Software "MrMan&Fam" wrote in message ... Whenever I close up the spreadsheet with the macro I made with your help, when I go back to the spreadsheet again the macro is gone. I have to cut and paste all over again. Why? "Ron de Bruin" wrote: See the link I posted or use To for your own address and BCC for all other addresses http://www.rondebruin.nl/mail/folder2/files.htm -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... You must be right. I found the 2nd of the 3 pieces of mail I sent myself later that evening. Later THIS evening I want to run the macro on a subset of the REAL list. What about my question regarding sending the same document to only ONE address at a time instead of the entire column? Among other things, some e-mail programs look at mail with multiple "TO" addresses as SPAM but I also don't want any of the recipients to know how many I'm sending out. "Ron de Bruin" wrote: Hi Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? It is working, disable one account and send the mail,you will get one mail. Enabled the other account and you will see you will receive a mail I live in the Netherlands in Europe -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? "Ron de Bruin" wrote: Hi MrMan&Fam it is only sending e-mail to the 1st address That's because they all yours and you will receive them. See this example on my website http://www.rondebruin.nl/mail/folder2/files.htm It is bed time for me now ( 23:31) but is if you need help post back and I will help you tomorrow after work. -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... I can NOT believe it! Without knowing what the hell I was doing, I followed your instructions and did (I believe) everything right. As a test, I created a NEW column "C" with only 3 e-mail addresses (all mine), I edited the attachment name, the "CC", all went well. I even got a message saying that "the computer is sending mail. This could be a virus..." BUT, it is only sending e-mail to the 1st address. It's listing all the other addresses in the "TO" section but it is not sending to the other addresses. Only the 1st one. Also, can I have it put only ONE ""TO" address and make a separate e-mail for each address (not putting them all on the "TO" section). Thanks. Great job! "Ron de Bruin" wrote: Here we Go Only working if you use Outlook this example Open a new workbook InsertModule from the menu bar Paste the sub in there Change the path/filename to the word Doc in the code Alt-Q to go back to Excel Now in the C column of "Sheet1" fill in your addresses Save the file If you do Alt-F8 you get a list of your macro's Select "Mail_workbook_Outlook" and press Run You can also use a cell in "Sheet1" with the path/filename to the word Doc if you want.(post back if you need help with that) For testing you can change .Send to .display in the code You can see how it look like and press the Send button manual then -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Thanks so much for the prompt response. Your work looks brilliant but it's wasted on me. I don't know anything about programming or programming in Excel. I don't even know what to do with your program. However, if this is what it takes, I guess there's no easy way. Thanks, anyway. "Ron de Bruin" wrote: Hi Try this one with the e-mail addresses in "Sheet1" column C Visit also my site for more examples Sub Mail_workbook_Outlook() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim strto As String For Each cell In ThisWorkbook.Sheets("Sheet1") _ ..Columns("C").Cells.SpecialCells(xlCellTypeConsta nts) If cell.Value Like "*@*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = strto .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ("C:\test.doc") .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Say I have an Excel spreadsheet with one column of e-mail addresses. I want to send the same piece of e-mail (a WORD file) to each address. Is there an easy way? |
#20
|
|||
|
|||
I think you've got it!! Create your macrosave the workbookrun your macro
and close without saving... -- Don Guillett SalesAid Software "MrMan&Fam" wrote in message ... This might sound stupid to you but in fact, I did NOT. The spreadsheet has 138 e-mail addresses and I did not want to send to all at once. So, I would clear contents from a group of e-mail address cells leaving only the group I wanted to mail to. Once I ran the macro, I closed the file DELIBERATELY without saving. This way, when I opened it again, ALL the addresses would be back on. Then I would clear contents of another group of cells in order to send to a different set of addresses, etc. SO, unless you save the spreadsheet, the macro doesn't get saved? Thanks. "Don Guillett" wrote: You did save the workbook afterwards... -- Don Guillett SalesAid Software "MrMan&Fam" wrote in message ... Whenever I close up the spreadsheet with the macro I made with your help, when I go back to the spreadsheet again the macro is gone. I have to cut and paste all over again. Why? "Ron de Bruin" wrote: See the link I posted or use To for your own address and BCC for all other addresses http://www.rondebruin.nl/mail/folder2/files.htm -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... You must be right. I found the 2nd of the 3 pieces of mail I sent myself later that evening. Later THIS evening I want to run the macro on a subset of the REAL list. What about my question regarding sending the same document to only ONE address at a time instead of the entire column? Among other things, some e-mail programs look at mail with multiple "TO" addresses as SPAM but I also don't want any of the recipients to know how many I'm sending out. "Ron de Bruin" wrote: Hi Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? It is working, disable one account and send the mail,you will get one mail. Enabled the other account and you will see you will receive a I live in the Netherlands in Europe -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Each of my three e-mail addresses is a different account with a different name. Thanks for your help. I'm in New York. It is 5:40 PM. Where are you?? "Ron de Bruin" wrote: Hi MrMan&Fam it is only sending e-mail to the 1st address That's because they all yours and you will receive them. See this example on my website http://www.rondebruin.nl/mail/folder2/files.htm It is bed time for me now ( 23:31) but is if you need help post back and I will help you tomorrow after work. -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... I can NOT believe it! Without knowing what the hell I was doing, I followed your instructions and did (I believe) everything right. As a test, I created a NEW column "C" with only 3 e-mail addresses (all mine), I edited the attachment name, the "CC", all went well. I even got a message saying that "the computer is sending mail. This could be a virus..." BUT, it is only sending e-mail to the 1st address. It's listing all the other addresses in the "TO" section but it is not sending to the other addresses. Only the 1st one. Also, can I have it put only ONE ""TO" address and make a separate e-mail for each address (not putting them all on the "TO" section). Thanks. Great job! "Ron de Bruin" wrote: Here we Go Only working if you use Outlook this example Open a new workbook InsertModule from the menu bar Paste the sub in there Change the path/filename to the word Doc in the code Alt-Q to go back to Excel Now in the C column of "Sheet1" fill in your addresses Save the file If you do Alt-F8 you get a list of your macro's Select "Mail_workbook_Outlook" and press Run You can also use a cell in "Sheet1" with the path/filename to the word Doc if you want.(post back if you need help with that) For testing you can change .Send to .display in the code You can see how it look like and press the Send button manual then -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Thanks so much for the prompt response. Your work looks brilliant but it's wasted on me. I don't know anything about programming or programming in Excel. I don't even know what to do with your program. However, if this is what it takes, I guess there's no easy way. Thanks, anyway. "Ron de Bruin" wrote: Hi Try this one with the e-mail addresses in "Sheet1" column C Visit also my site for more examples Sub Mail_workbook_Outlook() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim strto As String For Each cell In ThisWorkbook.Sheets("Sheet1") _ ..Columns("C").Cells.SpecialCells(xlCellTypeConsta nts) If cell.Value Like "*@*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = strto .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ("C:\test.doc") .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "MrMan&Fam" wrote in message ... Say I have an Excel spreadsheet with one column of addresses. I want to send the same piece of e-mail (a WORD file) to each address. Is there an easy way? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel Column Headings Compatible with Outlook to import. | obelix | New Users | 1 | September 28th, 2004 05:19 PM |
Different column width for the same column within an Excel sheet? | Cathleen | Worksheet Functions | 2 | September 16th, 2004 01:27 PM |
Exporting a range of cells in Excel to Outlook 2003 | DennisF | Contacts | 8 | July 22nd, 2004 08:36 AM |
Mail Merge uses Outlook instead of Excel data source | Karminak | Mailmerge | 1 | May 21st, 2004 10:20 AM |
Mail merge Excel database to Excel "document" | Rick | Setting up and Configuration | 0 | October 29th, 2003 06:29 PM |