sooty8
04-30-2009, 08:45 AM
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
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