VBA sending variable charts

ChrisM92

New Member
Joined
Nov 4, 2020
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I've got a macro set up that sends tables of data out to each department manager.
I've now had a request to also add in a graph at the bottom of the email to show the same data in another format, which means my Chart name will be a variable the same as my To, Cc ect.

I've been googling different macro's to do this and as far as I can tell the below should be working, but I'm getting an error on my xChart saying "Object Variable or With block variable not set"
If I remove "Dim xChart As ChartObject" from my code I don't get this error, but it also then doesn't attach anything to my email

Column P contains all my Chart titles, which are essentially just "DeskName_Chart"

I'm sure this is something really simple that I'm missing, so apologies in advance!

VBA Code:
Sub email()

    Dim rng As Range
    Dim OutApp As Object, OutMail As Object
    Dim ws As Worksheet
    Dim i As Long, lRow As Long
    Dim xChartN As String
    Dim xChart As ChartObject
    Dim xChartParth As String
    
    Set OutApp = CreateObject("Outlook.Application")

    Set ws = ThisWorkbook.Sheets("Desk Vacancy Email")

    With ws
        lRow = .Range("J" & .Rows.Count).End(xlUp).Row

        For i = 2 To lRow
            Set OutMail = OutApp.CreateItem(0)

Set rng = Nothing
Set xChart = Nothing

t = ws.Range("o" & i).Value
xChart = ws.Range("P" & i).Value
          
On Error Resume Next
Set rng = ActiveSheet.Range(t).SpecialCells(xlCellTypeVisible)

    xChartPath = ThisWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
    xChart.Chart.Export xChartPath

On Error GoTo 0

            With OutMail
                .To = ws.Range("K" & i).Value
                .CC = ws.Range("L" & i).Value
                .Subject = ws.Range("M" & i).Value
                .HTMLBody = ws.Range("N" & i).Value & RangetoHTML(rng)
                .Attachments.Add xChartPath
                .Save
              
            End With
        Next i
    End With
  
    Call Email_2
      
    End Sub
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,123,217
Messages
5,600,378
Members
414,380
Latest member
p1electric

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top