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