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?

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

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

edited May 23 '17 at 12:06

Community

1
1

answered Feb 25 '14 at 16:18

E Mett

2,272
3
18
37

Hi thx for the suggestions. ShellExecute wants me to save the file before i can send it. I would have to find out how that works then. But correct me if i'm wrong, but I read that SendObject is ment for sending objects created in vba. And thats what my code does. It creates an Excel graph and opens that in Excel without saving it first. Are there any other methods starting from my code? – user3240443 Feb 26 '14 at 08:13
`SendObject` only sends built in objects, like I wrote in my answer. You will have to save the Excel file somewhere and then you can send it as an attachment using one of the methods I showed in my answer. – E Mett Feb 26 '14 at 08:36
Ah ok I will try to find out how to safe it as a file then tyvm :) I'll give you the accepted awnser after it works ;) – user3240443 Feb 26 '14 at 09:16
To save as a file: `oBook.SaveAs "C:\Temp\ExcelFileForAttachment.xls` – E Mett Feb 26 '14 at 10:19
I responded with an awnser because there was too much space required for my post sorry – user3240443 Feb 26 '14 at 11:10

score 0 · Answer 2 · answered Feb 26 '14 at 13:59

thanks alot for your help. The example test code that worked for me is:

Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
'Create e-mail item'
Set objMail = olApp.CreateItem(olMailItem)
Set objAttachments = objMail.Attachments

With objMail
    .Subject = "Weekly Rapport"
    .Body = "Hi xyz, here is your Weekly Rapport"
    .Recipients.Add "xyz@abc.com"
    .Recipients.ResolveAll
    .Display
End With

objAttachments.Add "C:\Users\USERNAME\Documents\graphs\Test123.xls", olByValue, 1, "Test123"
'objMail.Display
objMail.Send

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

2 Answers2