Save PDF and mail macro? (Excel 2003)

sirrusja

New Member
Joined
Sep 23, 2009
Messages
3
Dear All,

I want to combine saving Excel worksheet into PDF file and mail this file to some recipients. I've done this in Excel 2007 with no problems, but the task is to make it possible in Excel 2003 (I am kind of novice in script writing).
I've found separate scripts one for saving PDF file (using CutePDF) and another one to send worksheet without attachments. Here they are:
-------------------------------------------------------------------------

Sub Button1_Click()

' This line of code calls the Adobe PDF printer and runs the conversion.
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True

' This set of code tells the macro to pause for 2 seconds.
' This will allow for the PDF printer to run through its process and prompt you for a filename.
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

' This line of code specifies your directory as well as the cell or range which you want the filename to come from.
Filename = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf"

' This line of code sends the filename characters and the ENTER key to the active application (i.e. the prompt window). The "False" statement allows the macro to continue running without waiting for the keys to be processed.

SendKeys Filename & "{ENTER}", False

Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim emailDate As Date
Dim sAttachment As String

If Weekday(Date, vbSunday) = vbSunday Then
emailDate = Date - 0
ElseIf Weekday(Date, vbSunday) = vbMonday Then
emailDate = Date - 0
Else
emailDate = Date - 0
End If

sAttachment = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf"

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient = .Recipients.Add("test@test.com")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
.Subject = "Report for " & Format(emailDate, "dd mmm yyyyy")
.Body = "Please find attached file for " & Format(emailDate, "dd mmm yyyyy")
.Attachments.Add sAttachment
.Display
End With

End Sub

-------------------------------------------------------------------------
So the PDF file is created succesfully, but scrips stops everytime on the following line: Set oOutlook = CreateObject("Outlook.Application")

Any ideas? :confused:
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Have you added the Outlook libary in the Visual Basic Editor --> Tools --> Refrences ?

Thanks a lot Rasmus! I added Outlook library and my script went a liitle bit further now.
This time I stuck at the very end on the line: .Attachments.Add sAttachment

I guess this line shoud work with: sAttachment = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf" but it didn't.

Can you help with that?
 
Upvote 0
Hi eveybody!

I have found solution myself! :)
Just remove seconds value from the following lines:
Filename = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf"
and
sAttachment = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf"

So finally I can email PDF attachment from Excel 2003!!!
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top