Email Spreadsheet

J

jhicsupt

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: [email protected], Subject: "This confirms your order".

Thanks in advance.
 
J

jhicsupt

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.
 
R

Ron de Bruin

Hi jhicsupt
Sorry for the questions.
Click to expand...
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 "[email protected]", _
"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
 
J

jhicsupt

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?
 
R

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
 
J

jhicsupt

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.
 
J

jhicsupt

Everything is working. Now I want to get fancy.

Can I also rename the worksheet "EmployeeWorksheet-(myworksheetname.xls&
Sheet1 Cell B2 & Cell B3)
 
J

jhicsupt

Sub Mail_workbook_1()
ActiveWorkbook.SendMail "[email protected]", _
"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.
 
R

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 "[email protected]", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub
 
J

jhicsupt

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!
 
R

Ron de Bruin

Hi jhicsupt

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