PDA

View Full Version : Send worksheets in body of email(outlook)



keifus2001
08-12-2007, 07:51 AM
I created a template for work and I would like to create a macro that will copy and paste two different worksheets in the workbook into the body of an email in Outlook 2003. I would also like it to save the file in a folder with the date and time appended to the file name. I have read a lot of information on this problem and I am unable to make it work. Any help would be appreciated.

geekgirlau
08-12-2007, 06:38 PM
Can you post the code you have so far?

shasur
08-12-2007, 07:19 PM
Here is something that you can build on

http://vbadud.blogspot.com/2007/04/vba-email-automation-vba-mail.html

keifus2001
08-13-2007, 05:13 AM
Here is what I have so far. I am trying to automate this task as much as possible. I would like to Open a new Email, copy a range from SUMMARY,paste it into the body of the Email, copy a range from HO PASS,paste it into the body of the Email enter a subject in the subject line and send it to a distribution list in my contacts. If it is not too much trouble, I would also like it to print SUMMARY and HO PASS and save the file in a folder with the date and time. I know that it is a lot to ask, but I am trying to make the job easier for the people that aren't computer savy at all. If you could steer me in the right direction, I would be grateful.

Second part. What is the best way to learn VBA programming? I bought Excel VBA Programming for Dummies by John Walkenbach and the Excel 2003 Bible. Thank You.

Sub SendAndPrint()
'
' SendAndPrint Macro
' Macro recorded 8/11/2007 by Reformer
'
'
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")
Set MItem = OutlookApp.CreateItem(0)

EmailAddr = cell.Value


With MItem
.To = EmailAddr
.Subject = Subj
'.Body = Range("A1:K77")
.Display
End With
Range("A1:K77").Select
Selection.Copy
Application.CutCopyMode = False
Range("E29").Select
Sheets("HO PASS").Select
Selection.Copy
Application.CutCopyMode = False
End Sub