How do you export an Excel sheet to Outlook?

Question

Hello,

I need help with the final step in the EoD report I have created. The EoD is written up in excel, its full of info on the amount of hours we worked on different builds, employee breakdowns, etc.

I have been trying to find a way to use a script or Power Automate, to put the cell and their data into an Outlook email. However, all my searching keeps returning other stuff, like how to export outlook into excel or how to use excel to get an email address for an oncall list.
How do I fill an Outlook email with the data and formatting I have in my EoD Excel sheet? Copy/Pasting is not a good option as it looks really really ugly in Outlook sadly.

Any help would be greatly appreciated!

Answer 1

Hi,

I did some research and found how it is to be done.

1. Go to your Excel sheet and open your VBE.
2. Create a new module.
3. Paste the below code. (please make changes as per your requirements)
4. Close the VBA Editor.
5. Press Alt+f8 and select the SendExcelDataInExcel macro and click on "Run".

Sub SendExcelDataInEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim MyWorkbook As Workbook
    Dim MyWorksheet As Worksheet
    Dim StrBody As String
    Dim RecipientEmailAddress As String
    
    ' Create a reference to the Outlook application
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    ' Set the Excel workbook and worksheet
    Set MyWorkbook = ThisWorkbook ' Assumes you are working with the active workbook
    Set MyWorksheet = MyWorkbook.Sheets("SheetName") ' Replace "SheetName" with the name of your sheet
    
    ' Define the email subject
    OutMail.Subject = "End of Day Report"
    
    ' Add recipients (To, CC, BCC)
    RecipientEmailAddress = "recipient@example.com" ' Change this to the recipient's email address
    OutMail.To = RecipientEmailAddress
    OutMail.CC = "cc@example.com" ' You can add CC recipients
    OutMail.BCC = "bcc@example.com" ' You can add BCC recipients
    
    ' Define the email body
    StrBody = "Hello," & vbCrLf & vbCrLf & _
              "Please find the End of Day report attached." & vbCrLf & vbCrLf & _
              "Best regards," & vbCrLf & _
              "Your Name"
    
    ' Add body content to the email
    OutMail.Body = StrBody
    
    ' Attach the Excel workbook to the email
    MyWorkbook.Save ' Save the workbook
    MyWorkbook.Close ' Close the workbook
    OutMail.Attachments.Add MyWorkbook.FullName
    
    ' Display the email and send
    OutMail.Display
    'OutMail.Send ' Uncomment this line to send the email automatically
    
    ' Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing 
End Sub

Answer 2

You can try modifying this tutorial: https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/email-images-chart-table

Specifically, replace the script in the tutorial with the following:

function main(workbook: ExcelScript.Workbook) {
    return workbook.getWorksheet('Worksheet name here').getRange('Range address here').getImage();
}

This will grab an image of the specified range and return it to the Power Automate flow to send via email. Let me know if that helps, or if you have any questions!