Forum home » Delegate support and help forum » Microsoft Excel Training and help » microsoft excel training class - A macro to email a spreadsheet
|
|
|
The UK's most regular Training information: See also: |
|
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. |
microsoft excel training class - A macro to email a spreadsheet
Resolved · Low Priority · Version Standard
Reply Wed 4 Apr 2007, 15:41Delegate Alistair said...
Alistair has attended:
Excel Advanced course
A macro to email a spreadsheet
Hi,
When I try and record a macro to email a sheet or workbook, the macro seems to ignore the emailing bit altogether.
Here's the macro with comments marked by ****:
Sub Macro1()
'
'
' Macro recorded 04/04/2007 by ALi.
'
'
Calculate
Sheets("Individual Bill Pivot").Select
Sheets("Individual Bill Pivot").Copy Before:=Sheets(1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Range("B1").Select
Selection.Copy **** I PASTED INTO THE RECEIPENT FIELD
Sheets("Individual Bill Pivot (2)").Select
Application.CutCopyMode = False **** SHOULD SEND EMAIL AT THIS POINT
ActiveWindow.SelectedSheets.Delete
End Sub
Cheers
ALi.
For upcoming training course dates see: Pricing & availability
RE: A macro to email a spreadsheet
Alistair
After creating the sheet you need to put in the VBA code to e-mail the sheet. After the Sheets("Individual Bill Pivot (2)").Select add the following code:
With ActiveWorkbook
.SendMail Recipients:="me@home.com", _
Subject:="My Excel Workbook"
.Close SaveChanges:=False
End With
This sends the workbook as an attachment.
To email a single worksheet, copy the worksheet (using code) into a blank
one-sheet workbook and send with the above code.
Regards
Carlos
Reply Thu 12 Apr 2007, 09:11Delegate Alistair said...
RE: A macro to email a spreadsheet
Thanks,
Unfortunately I have to send 100 bills to 100 people so I would have to replace the
:="me@home.com"
bit with some sort of promt for an email address or a variable list of emial addresses.
Cheers
ALi.
Reply Thu 12 Apr 2007, 09:11Delegate Alistair said...
RE: A macro to email a spreadsheet
Thanks,
Unfortunately I have to send 100 bills to 100 people so I would have to replace the
:="me@home.com"
bit with some sort of promt for an email address or a variable list of emial addresses.
Cheers
ALi.
RE: A macro to email a spreadsheet
ALi
Then create a variable called for example "MailAddress" with a string type which you then use in place of "me@home.com"
Then create a routine with a loop that looks at a list of email addresses and as it loads a new address into the variable it then runs the e-mail sending code
Regards
Carlos
|