sending email to multiple addresses based on a cell value

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
161
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I presently have coding that will allow me to send an email template to multiple recipients based on a cell value in a column. My issue is that I am trying to duplicate the same process for a value in a different column and it the email addresses do not populate in the BCC filed of the email

VBA Code:
Sub SendRemind_Click()

Dim xOutApp As Object
Dim MailItem As Object
Dim xmailbody As Variant
Dim I As Integer
Dim Dest As Variant
Dim ws As Worksheet
Dim H As Integer

Set xOutApp = CreateObject("Outlook.Application")
Set MailItem = xOutApp.CreateItem(0)
Set ws = Worksheets("Inventory")

With MailItem
Dest = ""
For I = 1 To WorksheetFunction.CountA(Columns(7))
If Dest = "" And Cells(I, 7).Offset(0, 15) = "Send" Then
Dest = Cells(I, 7).Value
ElseIf Dest <> "" And Cells(I, 7).Offset(0, 15) = "Send" Then
Dest = Dest & ";" & Cells(I, 7).Value
End If
Next I

xmailbody = "Hi," & vbNewLine & vbNewLine & _
            "Your registration to the CFIA National Transfer Inventory is up for renewal" & vbNewLine & _
            "You are required to complete a registration form and re-enter ALL your desired locations (maximum of 10)" & vbNewLine & vbNewLine & _
            "You have received an email equal to the total amount of locations you had previously selected" & vbNewLine & _
            "You are only required to re-register once - if you received multiple emails, delete all other emails" & vbNewLine & vbNewLine & _
            "http://merlin/netapp/rdimsredirect/exthum2e.aspx?url=8174049" & vbNewLine & vbNewLine & _
            "You have 1 month to renew your registration - failure to do so will result in your removal from the inventory" & vbNewLine & vbNewLine & _
            "Thank you" & vbNewLine & _
            "(HRSC) HR Service Centre / Pool Management" & vbNewLine & vbNewLine & _
            " ---------------------------------------------------------------" & vbNewLine & vbNewLine & _
            "Bonjour," & vbNewLine & vbNewLine & _
            "Votre inscription au répertoire national de mutation de l'ACIA doit-être renouvelée" & vbNewLine & _
            "Vous devez remplir un formulaire d'inscription et identifier vos lieux désirés (maximum de 10)" & vbNewLine & vbNewLine & _
            "Vous avez reçu un courriel égal au nombre total de lieu que vous avez précédemment choisi" & vbNewLine & _
            "Il vous suffit de vous réinscrire qu'une seule fois - si vous avez reçu plus qu'un courriel, supprimez toutes les autres courriels" & vbNewLine & vbNewLine & _
            "http://merlin/netapp/rdimsredirect/exthum2f.aspx?url=8177397" & vbNewLine & vbNewLine & _
            "Vous avez 1 mois pour renouveler votre inscription - faute de soumission vous vera retiré du répertoire" & vbNewLine & vbNewLine & _
            "Merci" & vbNewLine & _
            "(CSRH) Centre de service RH / Gestion de répertoire"

                On Error Resume Next

.SentOnBehalfOfName = "CFIA.HRServiceCentre-CentredeServiceRH.ACIA@canada.ca"
.Bcc = Dest
.Subject = "RENEWAL - CFIA National Transfer Inventory  /  RENOUVELLEMENT - Répertoire national de mutation de l'ACIA"
.Body = xmailbody
.Display

End With

On Error GoTo 0
Set MailItem = Nothing
Set xOutApp = Nothing


End Sub

the code above works .

the difference between this one and the other email is the wording, which isn't the problem....

the coding that is working is pulling the email address from column G and the trigger word "Send" is in column V
the coding that doesn't work is pulling the email address from column G but the trigger word "Send" is in column X

both function are triggered by a command button

can someone please provide some insight as I am lost on how to fix this

thanks
Dan
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
This bit is looking at column X if a little strange way of referencing it:

VBA Code:
Cells(I, 7).Offset(0, 15)

Lets say I is 1. Then cells(1,7) is cell G1. If you offset by 0 rows and 15 columns from G1 you get V1. You could of course just have used:

VBA Code:
Cells(I, 22)

which directly references V1 without the offset.
 
Upvote 0
thank you for the easier option ...... it simplified the coding but still didn't work. I scanned the column, the coding was referencing, and noticed that 2 cells were returning an error message (#NUM) and once I fixed that...it worked.

again thank you for the simplification
 
Upvote 0

Forum statistics

Threads
1,223,964
Messages
6,175,658
Members
452,664
Latest member
alpserbetli

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