Results 1 to 2 of 2

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Can anyone help above?

    Many thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •