Output to PDF

[derek GCC]

I have a makro that allows me to output a excel sheet to email and attach a PDF copy ( code that works on excel 2010 ) dosent work on 2103 Ecel

Problem is here in this line

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfFilePath

ANY HELP / ADVICE Appreciated?

Derek




Sub EmailWithOutlook()

' Variables for starting path !
Dim startingPath As String: startingPath = "C:\Derek" ' startingPath = ThisWorkbook.Path

' Variables for the Outlook application !
Dim oApp As Object
Dim oMail As Object

' Variables for newly generated pdf document !
Dim pdfFileName As String
Dim pdfFilePath As String

Dim subject_line As String
Dim email_body As String
Dim strWord As String

'Variable declaration leve alone
Dim rngSearch As Range
Dim rngFound As Range
Dim rngFirst As Range

Dim address_seperator As String
Dim receive_email_address As String
Dim file_date As String

'The key word is
strWord = "email"

'Set variables for subject line etc
subject_line = "Hours July 18 "

'Email Body text
email_body = " Please check your hours and email me with any queries on or before Wed 25th (if you want errors fixed this month.) " & Chr(13) & "I will pay your wages into your bank on or around Thursday 26th. " & Chr(13) & " Please advise me of any discrepancies as soon as possible and I will investigate and fix" & Chr(13) & " " & Chr(13) & " " & Chr(13) & " " & Chr(13) & " " & Chr(13) & "Many thanks, Derek 07901 711 045"

Set rngSearch = Range("A:P")

'The address seperator
address_seperator = "; "

'Email address search
'Build the search criteria
Set rngFound = rngSearch.Find(What:=strWord, LookAt:=xlWhole, MatchByte:=False)

' Cant find the key word
If rngFound Is Nothing Then

Debug.Print "Nothing"
MsgBox " Idiot Im Unable to find the key word '" & strWord & "'"
Exit Sub

End If

Set rngFirst = rngFound

Do

With rngFound
Debug.Print "Email" & .Offset(, 1).Value;
receive_email_address = .Offset(, 1).Value & address_seperator
'Exits Sub if no email adress is present
'Inform user
If receive_email_address = "" Then MsgBox "There is no email address"
'On Click End Sub
If receive_email_address = "" Then Exit Sub
End With

Set rngFound = rngSearch.FindNext(rngFound)

Loop While rngFound.Address <> rngFirst.Address

' Turn off screen updating !
Application.ScreenUpdating = False

' Make date prefix !
file_date = Format(Date, "yyyy-mm")

' Create pdf full path !
pdfFileName = file_date & "_InExtremis" & ".pdf"
pdfFilePath = startingPath & Application.PathSeparator & pdfFileName

' Remove PDF document if it already exist in starting folder !
On Error Resume Next
Kill pdfFilePath
On Error GoTo 0

' Create PDF document !
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfFilePath

' Create and show the outlook mail item !
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = receive_email_address
.Subject = file_date & " " & subject_line
.Attachments.Add pdfFilePath
.Body = email_body
.Display
End With

' Remove temporary pdf file !
Kill pdfFilePath

' Restore screen updating and release Outlook !
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

End Sub

[jmcilhinney]

That's not VB.NET code. If it's an Excel macro then it's VBA. I've asked the mods to move this thread to the Office Development forum. Please be clear about what language you're using and read the descriptions of teach forum to find the one that actually relates to your issue.

[si_the_geek]

Welcome to VBForums

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

[jdc2000]

What error message, if any, are you getting? What is the pdfFilePath value?

[derek GCC]

What error message, if any, are you getting? What is the pdfFilePath value?

Runtime error 1004
the document may be open or error may havve been encountered when saving

When I debug , the line below is highlighted :

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfFilePath

Thanks for help and looking - much appreciated

[jdc2000]

What is the pdfFilePath value? Does that document already exist, and, if so, is it already open? If you are not changing the name each time, and have the .pdf file open, you could get the error message you received.

[derek GCC]

What is the pdfFIlePath value? Does that document already exist, and, if so, is it already open? If you are not changing the name each time, and have the .pdf file open, you could get the error message you received.

Thanks for the advice and taking the time to look - the weird thing is that the code works perfectly in excel 2010 its just now Ive move to 2013 that im getting error

The new doc is not open untill I run the makro - So Im not sure how it could be open - Im not a programer ...

Appreciate your help

Derek

[jdc2000]

Add this code to the macro just before the line that errors. Post the results here so we can see the path and file names.

MsgBox "Path: <" & pdfFilePath & ">" & vbCrLf & "Name: <" & pdfFileName & ">", ,"Values"

[derek GCC]

Add this code to the macro just before the line that errors. Post the results here so we can see the path and file names.

MsgBox "Path: <" & pdfFilePath & ">" & vbCrLf & "Name: <" & pdfFileName & ">", ,"Values"

Its fixed
The path didn't exhist so I created the path it was looking for ( when it displayed from your code ) and bingo it worked

very much appreciate your help

Derek