| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Email Spreadsheet
How do I email the entire active workbook?
I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#2
|
|||
|
|||
|
Hi jhicsupt
Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#3
|
|||
|
|||
|
I tried using your example, but where do I paste the code. How is the email
generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#4
|
|||
|
|||
|
Hi jhicsupt
Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#5
|
|||
|
|||
|
This is almost it!!!!!
Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#6
|
|||
|
|||
|
Hi
This will stop the macro and you must fill in the address ActiveWorkbook.SendMail "", _ Or if you use one of the Outlook object model examples from my site use .Display instead of .Send -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This is almost it!!!!! Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#7
|
|||
|
|||
|
Is there a way to include in the subject line the contents of cell A1? The
reason I want to include this is because the employee's name is in A1 and I want the subject to appear as I have hundreds of emails that I will be receiving. Subject: "Thank you for your order - Smith" Thanks again. "Ron de Bruin" wrote: Hi This will stop the macro and you must fill in the address ActiveWorkbook.SendMail "", _ Or if you use one of the Outlook object model examples from my site use .Display instead of .Send -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This is almost it!!!!! Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#8
|
|||
|
|||
|
See
http://www.rondebruin.nl/mail/tips1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Is there a way to include in the subject line the contents of cell A1? The reason I want to include this is because the employee's name is in A1 and I want the subject to appear as I have hundreds of emails that I will be receiving. Subject: "Thank you for your order - Smith" Thanks again. "Ron de Bruin" wrote: Hi This will stop the macro and you must fill in the address ActiveWorkbook.SendMail "", _ Or if you use one of the Outlook object model examples from my site use .Display instead of .Send -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This is almost it!!!!! Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#9
|
|||
|
|||
|
Everything is working. Now I want to get fancy.
Can I also rename the worksheet "EmployeeWorksheet-(myworksheetname.xls& Sheet1 Cell B2 & Cell B3) "Ron de Bruin" wrote: See http://www.rondebruin.nl/mail/tips1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Is there a way to include in the subject line the contents of cell A1? The reason I want to include this is because the employee's name is in A1 and I want the subject to appear as I have hundreds of emails that I will be receiving. Subject: "Thank you for your order - Smith" Thanks again. "Ron de Bruin" wrote: Hi This will stop the macro and you must fill in the address ActiveWorkbook.SendMail "", _ Or if you use one of the Outlook object model examples from my site use .Display instead of .Send -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This is almost it!!!!! Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#10
|
|||
|
|||
|
Hi
Show me the macro that you use now I change it for you then tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Everything is working. Now I want to get fancy. Can I also rename the worksheet "EmployeeWorksheet-(myworksheetname.xls& Sheet1 Cell B2 & Cell B3) "Ron de Bruin" wrote: See http://www.rondebruin.nl/mail/tips1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Is there a way to include in the subject line the contents of cell A1? The reason I want to include this is because the employee's name is in A1 and I want the subject to appear as I have hundreds of emails that I will be receiving. Subject: "Thank you for your order - Smith" Thanks again. "Ron de Bruin" wrote: Hi This will stop the macro and you must fill in the address ActiveWorkbook.SendMail "", _ Or if you use one of the Outlook object model examples from my site use .Display instead of .Send -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This is almost it!!!!! Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#11
|
|||
|
|||
|
Sub Mail_workbook_1()
ActiveWorkbook.SendMail ", _ "Thank you for your order" & " - " & Sheets("key").Range("c8").Value & "-" & Sheets("key").Range("c5").Value & " " & Sheets("key").Range("c4").Value End Sub The only thing I want to do now is to rename the spreadsheet when it is sent in the email. Thanks again. "Ron de Bruin" wrote: Hi Show me the macro that you use now I change it for you then tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Everything is working. Now I want to get fancy. Can I also rename the worksheet "EmployeeWorksheet-(myworksheetname.xls& Sheet1 Cell B2 & Cell B3) "Ron de Bruin" wrote: See http://www.rondebruin.nl/mail/tips1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Is there a way to include in the subject line the contents of cell A1? The reason I want to include this is because the employee's name is in A1 and I want the subject to appear as I have hundreds of emails that I will be receiving. Subject: "Thank you for your order - Smith" Thanks again. "Ron de Bruin" wrote: Hi This will stop the macro and you must fill in the address ActiveWorkbook.SendMail "", _ Or if you use one of the Outlook object model examples from my site use .Display instead of .Send -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This is almost it!!!!! Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#12
|
|||
|
|||
|
Hi jhicsupt
The second example on the page show you how to do this http://www.rondebruin.nl/mail/folder1/mail1.htm It save with this line wbname = "C:/" & wb1.Name & " " & _ Format(Now, "dd-mm-yy h-mm-ss") & ".xls" You can use cell reference also in this string If you need more help post back Sub Mail_Workbook_2() Dim wb1 As Workbook Dim wb2 As Workbook Dim wbname As String Application.ScreenUpdating = False Set wb1 = ActiveWorkbook wbname = "C:/" & wb1.Name & " " & _ Format(Now, "dd-mm-yy h-mm-ss") & ".xls" wb1.SaveCopyAs wbname Set wb2 = Workbooks.Open(wbname) With wb2 .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "Thank you for your order" & " - " & Sheets("key").Range("c8").Value & "-" & Sheets("key").Range("c5").Value & " " & Sheets("key").Range("c4").Value End Sub The only thing I want to do now is to rename the spreadsheet when it is sent in the email. Thanks again. "Ron de Bruin" wrote: Hi Show me the macro that you use now I change it for you then tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Everything is working. Now I want to get fancy. Can I also rename the worksheet "EmployeeWorksheet-(myworksheetname.xls& Sheet1 Cell B2 & Cell B3) "Ron de Bruin" wrote: See http://www.rondebruin.nl/mail/tips1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Is there a way to include in the subject line the contents of cell A1? The reason I want to include this is because the employee's name is in A1 and I want the subject to appear as I have hundreds of emails that I will be receiving. Subject: "Thank you for your order - Smith" Thanks again. "Ron de Bruin" wrote: Hi This will stop the macro and you must fill in the address ActiveWorkbook.SendMail "", _ Or if you use one of the Outlook object model examples from my site use .Display instead of .Send -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This is almost it!!!!! Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#13
|
|||
|
|||
|
This worked great.
After the user clicks the button, I want to unprotect the document and then put the word "Your order was sent" in cell H9. Then protect the document back and save the file. Hopefully my last question, but this is unbelievable help! "Ron de Bruin" wrote: Hi jhicsupt The second example on the page show you how to do this http://www.rondebruin.nl/mail/folder1/mail1.htm It save with this line wbname = "C:/" & wb1.Name & " " & _ Format(Now, "dd-mm-yy h-mm-ss") & ".xls" You can use cell reference also in this string If you need more help post back Sub Mail_Workbook_2() Dim wb1 As Workbook Dim wb2 As Workbook Dim wbname As String Application.ScreenUpdating = False Set wb1 = ActiveWorkbook wbname = "C:/" & wb1.Name & " " & _ Format(Now, "dd-mm-yy h-mm-ss") & ".xls" wb1.SaveCopyAs wbname Set wb2 = Workbooks.Open(wbname) With wb2 .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "Thank you for your order" & " - " & Sheets("key").Range("c8").Value & "-" & Sheets("key").Range("c5").Value & " " & Sheets("key").Range("c4").Value End Sub The only thing I want to do now is to rename the spreadsheet when it is sent in the email. Thanks again. "Ron de Bruin" wrote: Hi Show me the macro that you use now I change it for you then tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Everything is working. Now I want to get fancy. Can I also rename the worksheet "EmployeeWorksheet-(myworksheetname.xls& Sheet1 Cell B2 & Cell B3) "Ron de Bruin" wrote: See http://www.rondebruin.nl/mail/tips1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Is there a way to include in the subject line the contents of cell A1? The reason I want to include this is because the employee's name is in A1 and I want the subject to appear as I have hundreds of emails that I will be receiving. Subject: "Thank you for your order - Smith" Thanks again. "Ron de Bruin" wrote: Hi This will stop the macro and you must fill in the address ActiveWorkbook.SendMail "", _ Or if you use one of the Outlook object model examples from my site use .Display instead of .Send -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This is almost it!!!!! Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
|
#14
|
|||
|
|||
|
Hi jhicsupt
Do you want that word only in the workbook you send or in both workbooks -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This worked great. After the user clicks the button, I want to unprotect the document and then put the word "Your order was sent" in cell H9. Then protect the document back and save the file. Hopefully my last question, but this is unbelievable help! "Ron de Bruin" wrote: Hi jhicsupt The second example on the page show you how to do this http://www.rondebruin.nl/mail/folder1/mail1.htm It save with this line wbname = "C:/" & wb1.Name & " " & _ Format(Now, "dd-mm-yy h-mm-ss") & ".xls" You can use cell reference also in this string If you need more help post back Sub Mail_Workbook_2() Dim wb1 As Workbook Dim wb2 As Workbook Dim wbname As String Application.ScreenUpdating = False Set wb1 = ActiveWorkbook wbname = "C:/" & wb1.Name & " " & _ Format(Now, "dd-mm-yy h-mm-ss") & ".xls" wb1.SaveCopyAs wbname Set wb2 = Workbooks.Open(wbname) With wb2 .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "Thank you for your order" & " - " & Sheets("key").Range("c8").Value & "-" & Sheets("key").Range("c5").Value & " " & Sheets("key").Range("c4").Value End Sub The only thing I want to do now is to rename the spreadsheet when it is sent in the email. Thanks again. "Ron de Bruin" wrote: Hi Show me the macro that you use now I change it for you then tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Everything is working. Now I want to get fancy. Can I also rename the worksheet "EmployeeWorksheet-(myworksheetname.xls& Sheet1 Cell B2 & Cell B3) "Ron de Bruin" wrote: See http://www.rondebruin.nl/mail/tips1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... Is there a way to include in the subject line the contents of cell A1? The reason I want to include this is because the employee's name is in A1 and I want the subject to appear as I have hundreds of emails that I will be receiving. Subject: "Thank you for your order - Smith" Thanks again. "Ron de Bruin" wrote: Hi This will stop the macro and you must fill in the address ActiveWorkbook.SendMail "", _ Or if you use one of the Outlook object model examples from my site use .Display instead of .Send -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... This is almost it!!!!! Is there a way to not automatically send it? In other words, just get it to Outlook and then have the user send it from Outlook? "Ron de Bruin" wrote: Hi jhicsupt Sorry for the questions. No problem We open the VBA editor with Alt-F11 We use InsertModule to create a new module We copy a macro from this page http://www.rondebruin.nl/mail/folder1/mail1.htm in this module Sub Mail_workbook_1() ActiveWorkbook.SendMail ", _ "This is the Subject line" End Sub Change it to your mail address Now we go back to Excel with Alt-q Save the file When you use Alt-F8 you will get your list with macro's Select "Mail_workbook_1" and press run Or add a button from the Forms toolbar and assign this macro to it Or add a button from the control toolbox and enter the macro name in the click event See help for help about the buttons -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... I tried using your example, but where do I paste the code. How is the email generated? Is there a button that needs to be added so user would click it to send the email? Sorry for the questions. "Ron de Bruin" wrote: Hi jhicsupt Use one of the examples from my site http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jhicsupt" wrote in message ... How do I email the entire active workbook? I want it to go to a specific person with a specific subject. In other words, to: , Subject: "This confirms your order". Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I import email addresses from Excel into other programs? | Excel Discussion (Misc queries) | |||
| Spreadsheet merging problems | Excel Worksheet Functions | |||
| Excel Spreadsheet email attachment unable to open in Outlook Expr. | Excel Discussion (Misc queries) | |||
| email portion of spreadsheet as attachment | Excel Discussion (Misc queries) | |||
| How do I cancel sending a spreadsheet by email? | Excel Discussion (Misc queries) | |||
Linear Mode
