Sending an Excel object from Access with SendObject, no attachment in e-mail

Question

Dear StackOverflowers,

I'm trying to send an Excel Object from Access with SendObject in VBA.

I have the code that makes the graph:

    Dim oXL As Object        ' Excel application
    Dim oBook As Object      ' Excel workbook
    Dim oSheet As Object     ' Excel Worksheet
    Dim oChart As Object     ' Excel Chart


    Const cNumCols = 100      ' Number of points in each Series
    Const cNumRows = 26       ' Number of Series

    ReDim aTemp(1 To cNumRows, 1 To cNumCols)   

    Set oXL = CreateObject("Excel.application")
    Set oBook = oXL.Workbooks.Add
    Set oSheet = oBook.Worksheets.Item(1)

Dim rs01 As DAO.Recordset
Set rs01 = CurrentDb.OpenRecordset("SELECT * FROM qryWOperweekCombined")

Dim Teller As Integer
Teller = 0
Dim iRow As Integer
iRow = 1
Dim iCol As Integer
iCol = 5

With rs01
If .RecordCount > 0 Then
    .MoveLast
    TotRecords = .RecordCount
    .MoveFirst   
           For Teller = 1 To TotRecords
                 aTemp(iRow, 1) = !Week
                 aTemp(iRow, 2) = !Total
                 aTemp(iRow, 3) = !companyk
                 aTemp(iRow, 4) = !companyv
                .MoveNext
                iRow = iRow + 1
            Next Teller
    oSheet.Range("A1").Resize(cNumRows, cNumCols).Value = aTemp
End If
End With

    Set oChart = oSheet.ChartObjects.Add(200, 1, 745, 380).Chart

oChart.SetSourceData Source:=oSheet.Range("A1:D26")

    oXL.Visible = True

oChart.HasLegend = True
oChart.HasTitle = True

oChart.SeriesCollection(4).ApplyDataLabels
oChart.SeriesCollection(4).DataLabels.Format.TextFrame2.TextRange.Font.Size = 7
oChart.SeriesCollection(2).ApplyDataLabels
oChart.SeriesCollection(2).DataLabels.Format.TextFrame2.TextRange.Font.Size = 7
oChart.SeriesCollection(3).ApplyDataLabels
oChart.SeriesCollection(3).DataLabels.Format.TextFrame2.TextRange.Font.Size = 7

oSheet.Columns("A:A").ColumnWidth = 18.71

    oChart.SeriesCollection(1).Name = "=""Dates"""
    oChart.SeriesCollection(1).XValues = "=Sheet1!$A:$A"
    oChart.SeriesCollection(2).Name = "=""Total"""
    oChart.SeriesCollection(2).XValues = "=Sheet1!$B:$B"
    oChart.SeriesCollection(3).Name = "=""companyk"""
    oChart.SeriesCollection(3).XValues = "=Sheet1!$C:$C"
    oChart.SeriesCollection(4).Name = "=""companyv"""
    oChart.SeriesCollection(4).XValues = "=Sheet1!$D:$D"
    oChart.SeriesCollection(1).Delete
    oChart.SeriesCollection(1).Name = "=""Total"""
    oChart.SeriesCollection(1).XValues = "=Sheet1!$A:$A"

oChart.SeriesCollection(1).Interior.Color = vbBlue
oChart.SeriesCollection(2).Interior.Color = vbGreen
oChart.SeriesCollection(3).Interior.Color = vbRed

oChart.SeriesCollection(1).Trendlines.Add
oChart.SeriesCollection(2).Trendlines.Add
oChart.SeriesCollection(3).Trendlines.Add

    oChart.SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:= _
        2, Forward:=1, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False, Name:="Average").Select
    oChart.SeriesCollection(2).Trendlines.Add(Type:=xlMovingAvg, Period:= _
        2, Forward:=1, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False, Name:="Average").Select
    oChart.SeriesCollection(3).Trendlines.Add(Type:=xlMovingAvg, Period:= _
        2, Forward:=1, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False, Name:="Average").Select

With oChart.SeriesCollection(1).Trendlines(1).Border
 .ColorIndex = 5
 .Weight = xlThick
 .LineStyle = xlContinuous
 End With

 With oChart.SeriesCollection(1).Trendlines(2).Border
 .ColorIndex = 5
 .Weight = xlMedium
 .LineStyle = xlContinuous
 End With

With oChart.SeriesCollection(2).Trendlines(1).Border
 .ColorIndex = 4
 .Weight = xlThick
 .LineStyle = xlContinuous
 End With

 With oChart.SeriesCollection(2).Trendlines(2).Border
 .ColorIndex = 4
 .Weight = xlMedium
 .LineStyle = xlContinuous
 End With

With oChart.SeriesCollection(3).Trendlines(1).Border
 .ColorIndex = 3
 .Weight = xlThick
 .LineStyle = xlContinuous
 End With

 With oChart.SeriesCollection(3).Trendlines(2).Border
 .ColorIndex = 3
 .Weight = xlMedium
 .LineStyle = xlContinuous
 End With

 oChart.Legend.Position = xlBottom

 oChart.SetElement (msoElementChartTitleCenteredOverlay)

 oChart.HasTitle = True
 oChart.ChartTitle.Text = "Workorders per week - last 26 weeks"

   oSheet.Visible = True
   oXL.UserControl = True

And I have a code that sends an e-mail:

Dim varName As Variant
Dim varCC As Variant
Dim varSubject As Variant
Dim varBody As Variant

varName = "name@server.com"
varCC = "name2@server2.com"

varSubject = "Hello"

varBody = "Text bla bla bla"

DoCmd.SendObject , oXL, acFormatXLS, varName, varCC, , varSubject, varBody, False, False

When I combine these I get a code that does send an e-mail, but it arrives without the excel attachment (also it's still opening Excel, but i'll figure that out later.

These 2 codes are combined in 1 sub.

Any ideas why it's not sending the attachment? Am I using the wrong object name (oXL) because oSheet and oBook are not working. Or could it have something to do with excel still being opened?

Answer 1

SendObject is for Ms Access objects such as a query, form or report.

You can also use it for sending an email with no attachment, if you leave the first two parameters empty.

You are sending an Excel object in the second parameter which command is ignoring.

Instead you should use an Outlook object to send an Excel file as an attachment by mail, see stackoverflow question here.

Alternatively, you could use ShellExecute