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
Bookmarks