Excel send different emails depending on cell value when command button clicked

[ElBB23]

Hi all,

I have been searching for a good day (if not more) on this so, I apologise if it has been answered before.

I have a spreadsheet with a command button basically when I click the button what I want to happen is:-

• Excel looks at value of a specific cell (B2)
• Based on the value in that cell an email is populated and sent to a list of email addresses on another sheet (list can vary in length depending on if I take or remove people from it)
• Within the email (other than the email addresses) cell range B4:F15 is copied and pasted into the body of the email
• Email is sent automatically

I am assuming this will be some sort of if function within the command button click that calls another VBA based on the cell value but I'm really struggling.

Thanks for the help in advance.

 

[Logit]

.
This is the "bible" on VBA email : https://www.rondebruin.nl/win/s1/outlook/mail.htm


Here is part of the code you are seeking :

Option Explicit




Sub mailTableWithGridLines()
Dim OutApp As Object
Dim OutMail As Object
Dim vInspector, GetInspector, wEditor As Variant




Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
    .To = "yo momma@nowhere.com"
    .CC = "xyz@anc.com"
    .BCC = "abc@xyz.com"
    .Subject = "Test"
    .Body = "Dear" & "Macro " & vbCrLf
    .Display
    ActiveSheet.Range("B4:R15").Copy		'<-- edit range to be copied here
    Set vInspector = OutMail.GetInspector
    Set wEditor = vInspector.WordEditor


    wEditor.Application.Selection.Start = Len(.Body)
    wEditor.Application.Selection.End = wEditor.Application.Selection.Start


    wEditor.Application.Selection.Paste


.Display
End With
Application.CutCopyMode = False
End Sub