Email Template Macro

[rdifu]

Hello! I'm trying to make an excel macro and was wondering if anyone has already done this before. I have an email that I send out every week that is always formatted exactly the same with the same wording and the numbers in it just changes. Essentially kind of like a not as fun businessy mad libs. Each week the new numbers that go out are in the same place in a spreadsheet that I have and update. The goal is to run a macro that will create the email template and reference the cells with the numbers and add them in for me. I hope this makes sense, if anyone has done it before or is able to show me an example, I'd appreciate it a lot. Thank you!!

 

[Logit]

What "numbers" ? are you sending and "where do they go on your sheet" ? Do you want to provide an example of your workbook or what you perceive will be your layout ? Makes it a lot easier for us to provide you an answer.

Option Explicit

Sub SndEmail()
Dim nConfirmation As Integer
Dim OutApp, OutMail As Object
Dim olMailItem

nConfirmation = MsgBox("Do you want to send an email notification about the sheet updating now?", vbInformation + vbYesNo, "Mail Sheet Updates")

    If nConfirmation = vbYes Then
        ActiveWorkbook.Save
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(olMailItem)
        
            With OutMail
                .To = "Test"
                .CC = ""
                .Subject = "Postnatal Urgent Spreadsheet Updated"
                .Body = "Hello, The Postnatal Urgent Slides Spreadsheet has been updated." & "   " & Range("B2").Value
                .Display
            End With
    End If
End Sub

REQUIRES SOME NUMBER IN B2