Invoicing VBA Solution - Please Help

[Tenacious1]

Hi there

There are 16 properties made up of 54 tenants. We arrange the insurance and pay a rounded premium for the entire portfolio.

The total premium has been apportioned per property already by the insurance company, and I have now apportioned the premium per tenant per property within the 'Data'.

I am a beginner with VBA but what I would like to do is hit a button and create an invoice for each tenant (in PDF) based on the premium data entered in excel, and to save it to a folder.

I understand very basic VBA so I am not coming in completely blind.

The layout of the headers in the data based on the information required for an invoice is as follows (in order from top of the invoice to bottom)

Columns:

Tenants name: F
Address: C
Premium: U


Any help would be greatly appreciated. Thanks

 

[Tenacious1]

Still the same message. I must be doing something wrong or have missed something.

On a positive note I have added the certificate and replicated the invoicing script and that works fine.

 

[Yongle]

1. Can you post the complete code that you are using (both macros) enclosed in code tags
- click on the # icon above post window FIRST and then paste your code between the 2 code tags that appear
- amend nothing other than any strings that are sensitive (path names etc)

2. This morning I (reluctantly) activated Outlook for the first time on my laptop and ran the code
- it ran without a problem and so we can get it working for you

3. Which column in your sheet contains the email addresses?

4. Which version of Excel are you running? (it should not matter )

 

[Tenacious1]

Hi this is the code before your last suggested change

Column AI or 28 contains the email addresses.

Using excel 2016

Sub InvoiceTenant()

Const fPath = "C:\temp"
Dim rng As Range, tenant As Range, Addr As Variant, adj As String, fName As String
Set rng = Range("G2", Range("G" & Rows.Count).End(xlUp))
adj = Format(Date, " dd-mm-yy ")
Sheets("Invoice").Activate
    For Each tenant In rng
        With ActiveSheet
            .Range("B4").Value = tenant
            .Range("B18").Value = tenant.Offset(, -1)
            .Range("B5").Value = tenant.Offset(, -4)
            .Range("B6").Value = tenant.Offset(, -3)
            .Range("B7").Value = tenant.Offset(, -2)
            .Range("G17").Value = tenant.Offset(, 15)
            .Range("H17").Value = tenant.Offset(, 22)
            .Range("G26").Value = tenant.Offset(, 16)
            .Range("H26").Value = tenant.Offset(, 23)
            .Range("G7").Value = tenant.Offset(, 27)
            fName = fPath & "\" & "Insurance " & adj & tenant
            .Range("B2:E37").ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName
             eAddr = tenant.Offset(, 28)
            Call SendEmail(tenant.Value, eAddr, fName)
        End With
    Next
End Sub


Private Sub SendEmail(tenant As String, eAddr As String, fName As String)
'Remember to add reference to Microsoft Outlook Object library (under VBA \ Tools \ References)
    Dim obMail As Outlook.MailItem
    Dim Greeting, WrdStr As String, EndStr As String, BodyTxt As String, Subj As String
'build strings
    Subj = "Insurance Premium Allocation"
    Greeting = "Dear " & tenant
    WrdStr = "Attached is invoice for your insurance...etc"
    EndStr = "Yours sincerely" & vbCr & "Yongle"
    BodyTxt = Greeting & vbCr & WrdStr & vbCr & EndStr
     fName = fName & ".pdf"
    On Error Resume Next
    Set obMail = Outlook.CreateItem(olMailItem)
'send emails
    Set obMail = Outlook.CreateItem(olMailItem)
        With obMail
            .To = eAddr
            .Subject = Subj
            .BodyFormat = olFormatPlain
            .Body = BodyTxt
            .Attachments.Add (fName)
            .Send
        End With


End Sub


Sub CertificateTenant()


Const fPath = "C:\temp"
Dim rng As Range, tenant As Range, Addr As Variant, adj As String, fName As String
Set rng = Range("G2", Range("G" & Rows.Count).End(xlUp))
adj = Format(Date, " dd-mm-yy ")
Sheets("Certificate").Activate
    For Each tenant In rng
        With ActiveSheet
            .Range("C16").Value = tenant
            .Range("C10").Value = tenant.Offset(, -5)
            .Range("D20").Value = tenant.Offset(, 3)
            .Range("D21").Value = tenant.Offset(, 4)
            .Range("D22").Value = tenant.Offset(, 12)
            .Range("D24").Value = tenant.Offset(, 9)
            .Range("C26").Value = tenant.Offset(, 8)
            .Range("D51").Value = tenant.Offset(, 17)
            .Range("D52").Value = tenant.Offset(, 24)
            .Range("F15").Value = tenant.Offset(, -4)
            .Range("G15").Value = tenant.Offset(, -1)
            .Range("C17").Value = tenant.Offset(, 26)
            .Range("C11").Value = tenant.Offset(, 25)
            fName = fPath & "\" & "Certificate " & adj & tenant
            .Range("B2:E54").ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName
        End With
    Next
End Sub




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub

 

[Yongle]

Thanks for the code I will look at it tomorrow for you

 

[Yongle]

A simple test to prove SendEmail sub works correctly

Run this macro AFTER amending
1. email address to a valid test email address
2. filepath and file name without the extension (ensure that the file is a PDF)

Check your sent box to see if an email indeed sent with PDF was attached

Sub MailTest()
    Const fname = "[COLOR=#000080]C:\FullPath\ToFile[/COLOR]\[COLOR=#006400]XXXX Sheet A 18-07-30[/COLOR]"            'extension is not required here
    Const eAddr = "[COLOR=#ff0000]yongle@provider.com[/COLOR]"
    Const tenant = "Joe"
    Call SendEmail(tenant, eAddr, fname)
End Sub