Bulk Emails with multiple variations

[ComputerNewbie1992]

Hi All,

I have an idea of what I would like the spreadsheet to do however I have ZERO experience with VBA or coding and I've relied heavily on formulas in the past. I've created an example spreadsheet to try to explain what I'm trying to achieve but basically, I'm looking for a spreadsheet which works as follows:

• Tick boxes of companies you want to send email to - if multiple are ticked, then multiple emails will be sent.
• 'Point of Contact' and 'Date' will be referred to in the email body.
• Press [Send] will provide a draft email which can be checked before sending.
  (In the past I've been unable to populate my default signature, as this will be utilised by different members of my team, I cannot have the signature as part of the email body)

Sorry I can't upload a Mini-sheet as my companies admin permissions are restrictive but I can send a copy of the spreadsheet via email if it'll be helpful.

Thanks in advance
Greg

To:	[Emails addresses associated with Company #]
Cc:	Boss@outlook.com
Subject:	[Generated from Cell]
Email Body:	Hi [Point of Contact], We wanted to let you know about an exciting product which we are looking to release to the market. Please provide your RSVP by [Date] to register your interest Regards [Signature]

 

[gordsky]

Does the body text of the email change each time to reflect the product (ie do you input that manually each time and want the email to reflect what is in that box?) or is that going to be the standard wording for each email.

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your example.

 

[ComputerNewbie1992]

Does the body text of the email change each time to reflect the product (ie do you input that manually each time and want the email to reflect what is in that box?) or is that going to be the standard wording for each email.

Hi Gordsky,

Yes the idea is for the email body to be a standard wording (with exception of the 'Point of Contact' and 'Date' field which will hopefully depend on their specific cells. If this isn't possible and it'll be easier for a cell which I type the whole email body into, this is okay also.

 

[ComputerNewbie1992]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your example.

Hi Mumps,

I'm unable to download the add-in as my company IT is restrictive.

 

[gordsky]

Hi Gordsky,

Yes the idea is for the email body to be a standard wording (with exception of the 'Point of Contact' and 'Date' field which will hopefully depend on their specific cells. If this isn't possible and it'll be easier for a cell which I type the whole email body into, this is okay also.

you can store the email body text within the VBA code if it is going to be identical everytime. If it isnt going to change then this is the easiest way. If you want slight variations each time then that is possible to

 

[ComputerNewbie1992]

you can store the email body text within the VBA code if it is going to be identical everytime. If it isnt going to change then this is the easiest way. If you want slight variations each time then that is possible to

Thanks Gordsky, if we could have it so we can make variations if needed, this will allow for future-proofing.

 

[mumps]

Working with check boxes can be tricky. Are they Form Controls or ActiveX Controls? It would be easier to program if instead of check boxes you simply typed in the letter "x" in column C beside the company name. Can you try to simply copy the data displayed in your picture in your original post and paste it directly here?

 

[ComputerNewbie1992]

Working with check boxes can be tricky. Are they Form Controls or ActiveX Controls? It would be easier to program if instead on check boxes you simply typed in the letter "x" in column C beside the company name. Can you try to simply copy the data displayed in your picture in your original post and paste it directly here?

Whichever is easier, I've only used the tickbox as a reference, if a column with 'x' would be easier I'd be happy with this too.

Does the below work?

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		Memo:	New release coming soon							Company A
3										A@outlook.com
4		Date	02/02/2022							B@outlook.com
5										C@outlook.com
6		Company A			PoC	Mr Smith
7		Company B			PoC	Mrs Jones				Company B
8		Company C			PoC	Mr Smith				D@outlook.com
9										E@outlook.com
10		To:	(Emails associated with ticked companies)							F@outlook.com
11		Cc:	Boss@outlook.com
12		Subject:	New release coming soon							Company C
13										G@outlook.com
14		Hi Mr Smith,								H@outlook.com
15		We wanted to let you know about an exciting product which we are looking to release to the market. Please provide your RSVP by 02/02/2022 to register your interest								I@outlook.com
16
17
18
19
20
21		Regards
22		[Email Signature]
23
24		SEND
25
Email list

Cell Formulas
Range	Formula
C12	C12	=C2

 

[mumps]

Place an "x" (lower case) in column C and run this macro:

Sub CreateEmails()
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, rng As Range, fnd As Range, x As Long
    Set OutApp = CreateObject("Outlook.Application")
    For Each rng In Range("C6", Range("C" & Rows.Count).End(xlUp))
        If rng = "x" Then
            Set fnd = Range("J:J").Find(rng.Offset(, -1).Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                x = fnd.CurrentRegion.Offset(1).Cells.Count - 1
                Set OutMail = OutApp.CreateItem(0)
                With OutMail
                    .To = Join(Application.WorksheetFunction.Transpose(Range("J" & fnd.Row + 1).Resize(x).Value), ";")
                    .cc = "Boss@outlook.com"
                    .Subject = Range("C2").Value
                    .HTMLBody = rng.Offset(, 3) & "<br><br>" & "We wanted to let you know about an exciting product which we are looking to release to the market." & "<br><br><br><br>" & "Regards,"
                    .Display
                End With
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub