Send Report as Email body

[meiteniite89]

Hello,

I have generated report from form and I need it to be dent in the body of the email.
Is it possible?
I have read loads of threads and put together this, see below.

Currently it sends an attachment from my computer, but not report. I know there is a lot wrong, but I just figure it out.

        Dim olApp As Outlook.Application
        Dim olMail As Outlook.MailItem

        'get application
        On Error Resume Next
        Set olApp = GetObject(, "Outlook.Application")
        If olApp Is Nothing Then Set olApp = New Outlook.Application
        On Error GoTo 0

        Set olMail = olApp.CreateItem(olMailItem)
        With olMail
        .Subject = "My email with attachment"
        .Recipients.Add "carl.sutcliffe@cummins.com"
        .Attachments.Add "C:\Users\jy871\Desktop\front page.xlsm"
        .Body = "Here is an email"

 

[theDBguy]

Hi. Are you saying you want the form or report embedded as an image in your email?

 

[meiteniite89]

Hi. Are you saying you want the form or report embedded as an image in your email?

I just want report to be displayed in the body of the email. Text of report.

 

[theDBguy]

I just want report to be displayed in the body of the email. Text of report.

Hi. One approach is to use an ADO recordset and its GetString method.

GetString Method (ADO) - ActiveX Data Objects (ADO)

GetString Method (ADO)

docs.microsoft.com

 

[arnelgp]

do you need to send the xlsm file as your email body.
you can convert it to text:

How to save as .txt in vba

I am looking to have my Macro save a new sheet that i created as a .txt file. this is the code i have so far. Sub Move() ' ' Move Macro ' ' Keyboard Shortcut: Ctrl+m ' Sheets("Sheet1").

stackoverflow.com

and use it as the .Body of your email:

.Body = ReadTextFile("thePathAndFilenameOfTextfile")

add this another function:

Public Function ReadTextFile(ByVal strPathFile As String)
    With CreateObject("Scripting.FileSystemObject")
        ReadTextFile = .OpenTextFile(strPathFile, 1, True, -20).ReadAll
    End With
End Function

 

[meiteniite89]

do you need to send the xlsm file as your email body.
you can convert it to text:

How to save as .txt in vba

I am looking to have my Macro save a new sheet that i created as a .txt file. this is the code i have so far. Sub Move() ' ' Move Macro ' ' Keyboard Shortcut: Ctrl+m ' Sheets("Sheet1").

stackoverflow.com

and use it as the .Body of your email:

.Body = ReadTextFile("thePathAndFilenameOfTextfile")

add this another function:

Public Function ReadTextFile(ByVal strPathFile As String)
    With CreateObject("Scripting.FileSystemObject")
        ReadTextFile = .OpenTextFile(strPathFile, 1, True, -20).ReadAll
    End With
End Function

It was xlsm file, but it was just to trial it out.
Maybe there is a way to put Report into a body of the email?

 

[theDBguy]

It was xlsm file, but it was just to trial it out.
Maybe there is a way to put Report into a body of the email?

Did you check out the GetString method?

 

[meiteniite89]

do you need to send the xlsm file as your email body.
you can convert it to text:

How to save as .txt in vba

I am looking to have my Macro save a new sheet that i created as a .txt file. this is the code i have so far. Sub Move() ' ' Move Macro ' ' Keyboard Shortcut: Ctrl+m ' Sheets("Sheet1").

stackoverflow.com

and use it as the .Body of your email:

.Body = ReadTextFile("thePathAndFilenameOfTextfile")

add this another function:

Public Function ReadTextFile(ByVal strPathFile As String)
    With CreateObject("Scripting.FileSystemObject")
        ReadTextFile = .OpenTextFile(strPathFile, 1, True, -20).ReadAll
    End With
End Function

ReadTextFileline throwns an error. Error code 5.

 

[Extra_Cover]

Try .HTMLBody. You will need to enable Microsoft HTML Object Library in VBA References.

bdy = "<img src=" & "pathandfilenameofyourreport" & " alt=""Logo"" title=""Logo"" style=""display:block"" width=""1240"" height=""1000"" />"

.HTLMBody = bdy

I have just completed a mass mail system and this works for me, although my file type is .jpg.