Email Spreadsheet

[Guest]

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: (e-mail address removed), Subject: "This confirms your order".

Thanks in advance.

 

[Ron de Bruin]

Hi jhicsupt

Use one of the examples from my site
http://www.rondebruin.nl/sendmail.htm

 

[Guest]

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]

Hi jhicsupt

Sorry for the questions.

No problem

We open the VBA editor with Alt-F11
We use Insert>Module 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 "(e-mail address removed)", _
"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

 

[Guest]

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]

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

 

[Guest]

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]

See
http://www.rondebruin.nl/mail/tips1.htm

 

[Guest]

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]

Hi

Show me the macro that you use now
I change it for you then tomorrow

 

[Guest]

Sub Mail_workbook_1()
ActiveWorkbook.SendMail "(e-mail address removed)", _
"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]

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 "(e-mail address removed)", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

 

[Guest]

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]

Hi jhicsupt

Do you want that word only in the workbook you send or in both workbooks