Setting up email to pull info from specific cells

[Aussie5380]

Hi there,

Still quite new to macros, but learning along the way. I have a button setup to open a new email in my spreadsheet, however i was wondering if i could have this email pulls info from a set of cells in excel?

This is the code im using, i just need some guidance on how to have this setup:

Sub Create_Email()
Dim appOutlook As Outlook.Application
Dim mEmail As Outlook.MailItem

Set appOutlook = New Outlook.Application
Set mEmail = Outlook.CreateItem(olMailItem)

With mEmail
.To = ""
.Subject = "Purchase Leave Application"
.HTMLBody = ""
.Display
End With
Set mEmail = Nothing
Set appOutlook = Nothing
End Sub


What i want the email to look like is this:

Hi (value from cell),

Payroll has processed your Purchase leave Application. The total cost is (value from cell). A deduction will be setup in your record to recover the amount of (value from cell) per pay, for the next (value from cell) pays.

Many thanks,

Payroll Services


The Cell's in question are:

B7:D7 (Name - these cells are merged)

K9 (total value)

K12 (total per pay)

E16:F17 (how many pays recovery - cells are merged)

I have tried to follow a few guides, but not having much luck formatting the email the right way (displays as a single line of text), and having the values from the cells displayed in the email.

Any assistance would be greatly appreciated!

 

[Sparcot]

Hello. Enjoy:

Sub Create_Email()
    Dim appOutlook As Outlook.Application
    Dim mEmail As Outlook.MailItem
    Dim StrBody1 As String
    Dim StrBody2 As String
    
    StrBody1 = "Hi " & ActiveSheet.Range("B7").Value & "," & "<br><br>"
    StrBody2 = "Payroll has processed your Purchase leave Application. The total cost is " _
        & ActiveSheet.Range("K9").Value & ". A deduction will be setup in your record to recover the amount of " _
        & ActiveSheet.Range("K12").Value & " per pay, for the next " _
        & ActiveSheet.Range("E16").Value & " pays." _
        & "<br><br>" & "Many thanks," & "<br><br>" & "Payroll Services"
    
    Set appOutlook = New Outlook.Application
    Set mEmail = Outlook.CreateItem(olMailItem)

    With mEmail
    .To = ""
    .Subject = "Purchase Leave Application"
    .HTMLBody = StrBody1 & StrBody2
    .Display
    End With
    Set mEmail = Nothing
    Set appOutlook = Nothing
End Sub