Samantha has attended:
Excel VBA Intro Intermediate course
Emailing an excel spreadsheet using vba
i have a macro that formats a file, but after formatting, i want to then email that file to a specific email address.
i tried to record the macro when doing it but no code was displayed in vba
can you please help?
thanks
sam
RE: emailing an excel spreadsheet using vba
Hi Sam
There is a simple routine that sends the active Workbook to an e-mail recepient. This is:
Sub EMailActiveWorkbook()
ActiveWorkbook.SendMail Recipients:="someone@home.com", Subject:="Our Excel Work For " & Format (Date, "dd/mmm/yy")
End Sub
The above code can be entered as a macroto be called from the Fromat macro, or you could add the code to your Fromat macro
Hope this helps
Carlos
RE: emailing an excel spreadsheet using vba
Hi carlos,
this didnmt seem to work.
error message came up saying "run time error 1004, method SendMail of object _Workbook failed"
any ideas? perhaps something that i dont have loaded into my excel?
thanks
sam
RE: emailing an excel spreadsheet using vba
Hi Sam
I ran the macro above on its own both from the code window and as a button in the toolbar.
In both cases it worked perfectly.
So I'm wondering if there is a problem with your company's Outlook settings
OR
if you are trying to refer to the Workbook to be sent by name.
If so the workbooks name and path have to be absolutely accurate otherwise you get a 1004 error for trying to e-mail a workbook that doesn't exist
Carlos
RE: emailing an excel spreadsheet using vba
hi carlos,
here is my code
'AUTOFIT COLUMNS
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
'go back to detailed sheet and get to A1
wsD.Select
Range("A1").Select
ActiveCell.FormulaR1C1 = Time
Sheets(ActiveSheet.Name).Move
'**CHANGE THIS DIRECTORY TO THE PLACE TO SAVE THE REPORT
ChDir "D:\DMG"
directory = "D:\DMG\"
'file name
filenamesave = "Automated " & FormatDateTime(Date, vbLongDate)
ActiveWorkbook.SaveAs Filename:=directory & filenamesave & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
'send as an email - addition 10/12/07
ActiveWorkbook.SendMail Recipients:="sam@datam.co.uk", Subject:=filenamesave
'close workbook
ActiveWorkbook.Close
so you can see that i move the worksheet, then save the workbook as a file name, then try to send it as an email.
i CAN do this manually so i'm not sure there is anything wrong with excel.
would it mnake a difference that i am using excel 2003?
thanks
sam
RE: emailing an excel spreadsheet using vba
Sam
I assume that everything works OK except the e-mail part of the code.
I took you e-mail line and declared FileNameSave as a String variable and used your text in it.
I ran the code and created a e-mail in the outbox of my Outlook with your FileNameSave value as subject.
Which means that your Outlook might be set to not allowing other applications to send an email.
On my system I get a warning informing me that an external program is trying to send an e-mail and is that OK.
You may need you IT department to change the settings on your computer to allow this.
Carlos
RE: emailing an excel spreadsheet using vba
ok thanks carlos,
have now established that it does NOT work on my laptop but DOES work on other pcs.
will have to get to the bottom of what is going on with my laptop.
so thank you very much for your help with this
cheers,
sam
