save and send worksheet as pdf

[rosajen]

Hi!!

I'm trying save the first sheet of my workbook as a pdf then attach to an outlook email then view the email before sending. Everything works except for getting the pdf to attach. My code is all activiated by the button click event. Range("T2") is the file path where it is saved. It's

="S:\Common\MANUALS\QUALITY\IS\Notes\Furnace Notes"&TEXT(H1,"mmddyy")

the part of the code in bold seems to be the only part not working. I'm using excel 2007

Please help!! thanks

Option Explicit
Sub SaveBtn_Click()
Dim filename As String
ThisFile = Range("T2").Value
With ActiveSheet
.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=ThisFile, _
OpenAfterPublish:=True
End With
Mail
End Sub

Sub Mail()
Dim filename As String
RDB_Mail_PDF_Outlook filename, "name@email.com", "Furnace Notes and Summary" & Range("H1"), "This is a summary of today's meeting" & vbNewLine & vbNewLine & "My name", False
End Sub

Function RDB_Mail_PDF_Outlook(filenamepdf As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = StrTo
.CC = ""
.BCC = ""
.Subject = StrSubject
.Body = StrBody
.Attachments.Add filenamepdf
.Display
End If
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Function
<!-- / message -->

 

[Derek Brown]

Hello and welcome to The Board.

Sub Mail()
Dim filename As String
RDB_Mail_PDF_Outlook filename, "name@email.com", "Furnace Notes and Summary" & Range("H1"), "This is a summary of today's meeting" & vbNewLine & vbNewLine & "My name", False
End Sub

In the above you are declaring 'filename' and passing it to the function without setting a value.