98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » excel course tate modern - Emailing an excel spreadsheet using vba
excel course tate modern - Emailing an excel spreadsheet using vba
Resolved · Low Priority · Version Standard
Thu 29 Nov 2007, 10:46Delegate Samantha said...
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
Mon 10 Dec 2007, 12:17Trainer Carlos said...
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
Mon 10 Dec 2007, 13:51Delegate Samantha said...
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
Mon 10 Dec 2007, 14:24Trainer Carlos said...
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
Mon 10 Dec 2007, 14:37Delegate Samantha said...
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
Mon 10 Dec 2007, 15:17Trainer Carlos said...
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
Tue 11 Dec 2007, 14:57Delegate Samantha said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Sorting List SubtotalsIf you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows. |
