Convert Sheet To PDF Before Emailing

[sooty8]

Hi

I have been using Outlook Express 6 for years emailing invoices to various clubs that I have to deal with - I use Office 2007 my problem is that the clubs I deal with some of them have replaced their computers and they use MS Works and therefore cannot open Excel - Have tried to educate them to download Excel Reader but its a struggle - most of the new computers have Adobe installed which they can open OK. The code I use is below could it be adapted to convert the sheet to PDF and then Email it to the correct Recipient as per the code.


Sub Send1Sheet_ActiveWorkbook()
'Create a new Workbook Containing 1 Sheet (left most) _
and sends as attachment.
Dim Recipient As String

On Error GoTo Error_Handler
startrowclubspayment = 2
rownumclubspayment = UserForm2.Cmd1.ListIndex + startrowclubspayment
Recipient = ActiveWorkbook.Sheets("ClubsPayment").Cells(rownumclubspayment, 18)

If Trim(Recipient) <> "" Then
ThisWorkbook.Sheets("Invoice").Copy

AttachmentName = CurDir & "\Invoice.xls"
ActiveWorkbook.SaveAs Filename:= _
AttachmentName _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

With ActiveWorkbook
.SendMail Recipients:=Recipient, _
Subject:="Invoice / Receipt" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
Else
MsgBox ("No E-Mail available")
End If

On Error GoTo 0
'Exit to avoid error handler
End

Error_Handler:
MsgBox ("E-Mail Failed - probably don't have the invoice sheet as activeworkbook")

End Sub


Many Thanks

Sooty8

[mdmackillop]

Try this

Sub Send1Sheet_ActiveWorkbook()
Dim Recipient As String
Dim CurDir As String
Dim AttachmentName As String
Dim msg As String, Subj As String
CurDir = "C:\AAA\" '<===Change to suit

Recipient = "mdm@vbax.com"
Subj = "Invoice / Receipt" & Format(Date, "dd/mmm/yy")

msg = "Hello World," & vbCrLf & vbCrLf
msg = msg & "Here is your invoice." & vbCrLf & vbCrLf
msg = msg & "Please pay it!!!"
If Trim(Recipient) <> "" Then

Sheets("Invoice").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
CurDir & "Invoice.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True _
, IgnorePrintAreas:=False, OpenAfterPublish:=False

AttachmentName = CurDir & "Invoice.pdf"
DoSendMail Recipient, Subj, msg, AttachmentName
End If
End Sub

Sub DoSendMail(Recip, Subj, msg, Attach)
Dim OLApp As Object
Dim EmailItem As Object
Dim EmailRecip As Object
Set OLApp = CreateObject("Outlook.Application")
Set EmailItem = OLApp.CreateItem(0)

Dim oRecipient As Object
Set oRecipient = EmailItem.Recipients.Add(Recip)
oRecipient.Type = 1

EmailItem.Body = msg
EmailItem.Attachments.Add Attach
EmailItem.Display 'Send
Set EmailItem = Nothing
Set OLApp = Nothing
End Sub

[sooty8]

Hi md

Thanks for the reply - have inserted your code into a new module and changed the directories as you suggested ran the Sub and it worked until it hit Create Object msgbox >>>> Run-time error '429' ActiveX component can't create object. I'm assuming it is because I still use Outlook Express ( I have used it for so long and as it happens I just like using it -- I suppose its like a favourite sweater that you won't throw away.) Do I really have to use Outlook? if so I suppose I will have to reinstall it.

Many Thanks

Sooty8

[mdmackillop]

I'm sure you can use Express. I don't have the code for it off-hand. I can check it out later if you don't find it first.

[mdmackillop]

Try here http://www.rondebruin.nl/sendmail.htm

[FrymanSMU]

Is there a special reference for the ExportAsFixedFormat command? I'm getting a Run-Time error '438' Object doesn't support this property or method.

Thanks

[sooty8]

Hi FrymanSMU

I really don't know - I'm a novice and have had tremendous help from everybody who answers questions on this Forum. Is the simple answer if using Office 2007 you have used the Add In for PDF which you can download from MS.

I'm still struggling and have actually installed Outlook and can't get it to read my Email List.

Cheers

Sooty8

[mdmackillop]

It's late here now but exporting to pdf is a 2007 facility. I'll look into Express tomorrow.