sending email to multiple addresses based on a cell value

[Blanchetdb]

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

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

 

[steve the fish]

This bit is looking at column X if a little strange way of referencing it:

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:

Cells(I, 22)

which directly references V1 without the offset.

 

[Blanchetdb]

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