Hyperlink to open invoice in Word format then convert to PDF and attach to an email

**lapot** · 2014-11-02, 12:16 AM

Dear all
My attached excel sheet have list invoices , Column B has invoice numbers and Colum E has hyperlinks which are coming from SQL server on the "invoice Sheet " (third sheet), these hyperlinks to open the invoices in WORD format; This is the location of the one invoice

"\\svr-storage3\Accounts_Data\Docs\H\H\A\HHA6\62\Bill ref 223615_458254_1.doc"

I want to add a textbox my UserForm and when I enter an invoice number in the textbox it will find the invoice number on Colum B and will activate its hyperlink to open the invoice in word format, then convert the invoice PDF before attaching to an email.

When I run the below code it attaches all the hyperlink invoices to separate emails but in word format with this line

Code:

attachement = Sheets("invoice").Cells(x, 5).Value

I like to add texbox invoice number enter function and conver the invoice PDF format before attaching to email.

Code:

Sub WordtoPdf()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long
Dim Ref1 As Long
Dim example As Range
Set example = Range("T:T,U:U,W:W,X:X")
Dim fnd As Range
Dim attachment As String
Dim x As Long
example.ClearContents
LastRow = Sheets("Rapor").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To LastRow

attachement = Sheets("invoice").Cells(x, 5).Value

Ref1 = Cells(x, 5).Value

mydate1 = Cells(x, 4).Value
mydate2 = mydate1

Cells(x, 20).Value = mydate2

datetoday1 = Date
datetoday2 = datetoday1

Cells(x, 21).Value = datetoday2

If mydate2 - datetoday2 < 3 Then

Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = Cells(x, 22).Value

With mymail
.Subject = "Payment Reminder"
.Body = "Dear Sir" _
& vbCrLf & "" _
& vbCrLf & "Our records is showing that we havent recived payment for our Invoice No: " & Ref1 _
& vbCrLf & "" _
& vbCrLf & "I will be greatful if you arrange payment aginst this invoice" _
& vbCrLf & "" _
& vbCrLf & "Kind Regards" _
& vbCrLf & "John Smith" _
& vbCrLf & "" _
& vbCrLf & ""
.Attachments.Add attachement
.Display
'.Send

Set mymail = Nothing

End If
End If

Next
Set myApp = Nothing
Set mymail = Nothing
End Sub

kind regards