Blanchetdb
Board Regular
- Joined
- Jul 31, 2018
- Messages
- 161
- Office Version
- 2016
- Platform
- 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
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
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