Basic VBA Help

[Columbo]

Total VB noob here so please excuse my lack of knowledge!

Currently setting up a spreadsheet for my Ops team to send out via at the end of each shift. I'm trying to automate where possible and am partially there but need a little help.

The below code is successfully attaching the full workbook and creating a new email via a macro button but I'd also like to get the contents of a worksheet named 'Summary' included in the body of the email when they press that same button to email.

Whats the best/ easiest way of doing this? Any help appreciated

  Sub Mail_workbook_Outlook_1()
    
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .to = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[dazzerd]

That could be challenging depending what you are trying to put in the email but it seems you are already running the vba from a workbook.

If the summary sheet exists in the workbook you are running the script from then you can just do something like

   .body = thisworkbook.sheets("Summary").range("A1").value
'this will but the contents of cell 
'a1 into the body of the text i think you can
'adapt from there

If you need to open another workbook then you need to add something like this to your script

Dim wb as workbook
Dim ws as worksheet

Set wb = workbooks.open("yourpath")
Set ws = wb.sheets("Summary") 'and then
   .body = ws.range("A1").value

You can actually use some html if you know what you are doing, just replace

   .body 'with
   .htmlbody = "your built up html string"

Oh and dont use on error resume next it basically tells the complier to ignore errors and continue the script. To keep it simple use on error goto eh and then a block beginng eh: with your error coding routine
and you dont need the .display unless you need to see the email before its sent, you can use just .send.