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

ElBB23

New Member
Joined
Feb 10, 2017
Messages
26
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
.
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 :

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top