VBA EMail distribution not working

[SupremeDr]

Hello,
[FONT=&quot]I've built a program in excel VBA that creates emails and attaches invoices based on a distribution list.
The program creates drafts of the email and saves them for review before we manually send them.
The issue is that every single e-mail came back undeliverable.
This is the e-mail I received
-----
From: System Administrator
Sent: Thursday, August 29, 2019 2:00 PM
Subject: Undeliverable: REDACTED
Your message did not reach some or all of the intended recipients.
Subject: REDACTED
Sent: 8/29/2019 2:00 PM
The following recipient(s) cannot be reached:
REDACTED@REDACTED.COM on
The recipient has been deleted or has no email address.
-----
I can copy/paste the email addresses in just fine but for some reason this method won't work. When we reviewed the draft everything looked fine I'm very confused. (Note that after the first few emails came back we just replaced the email addresses in the drafts. We didn't create new emails)

Here is the section of code that adds the emails.

Set Mail = objOutlook.CreateItem(olMailItem)
                        Mail.To = wsMLOG.Range("L" & i).Value & ";" & wsMLOG.Range("M" & i).Value & ";" & wsMLOG.Range("N" & i).Value & ";" & wsMLOG.Range("O" & i).Value & ";" & wsMLOG.Range("P" & i).Value & ";" & wsMLOG.Range("Q" & i).Value & ";" & wsMLOG.Range("R" & i).Value & ";" & wsMLOG.Range("S" & i).Value & ";" & wsMLOG.Range("T" & i).Value & ";" & wsMLOG.Range("U" & i).Value & ";" & wsMLOG.Range("V" & i).Value & ";" & wsMLOG.Range("W" & i).Value & ";" & wsMLOG.Range("X" & i).Value & ";" & wsMLOG.Range("Y" & i).Value & ";" & wsMLOG.Range("Z" & i).Value & ";" & wsMLOG.Range("AA" & i).Value & ";" & wsMLOG.Range("AB" & i).Value
                        Mail.Subject = wsMLOG.Range("B" & i).Value & " " & wsMLOG.Range("C" & i).Value
                        Mail.Body = Sheets("Sheet1").Range("A1").Text

[/FONT]

 

[SupremeDr]

I have some more information on this.

I changed my code to the following

mailAddress = wsMLOG.Range("L" & i).Value & ";" & wsMLOG.Range("M" & i).Value & ";" & wsMLOG.Range("N" & i).Text & ";" & wsMLOG.Range("O" & i).Text & ";" & wsMLOG.Range("P" & i).Text & ";" & wsMLOG.Range("Q" & i).Text & ";" & wsMLOG.Range("R" & i).Text & ";" & wsMLOG.Range("S" & i).Text & ";" & wsMLOG.Range("T" & i).Text & ";" & wsMLOG.Range("U" & i).Text & ";" & wsMLOG.Range("V" & i).Text & ";" & wsMLOG.Range("W" & i).Text & ";" & wsMLOG.Range("X" & i).Text & ";" & wsMLOG.Range("Y" & i).Text & ";" & wsMLOG.Range("Z" & i).Text & ";" & wsMLOG.Range("AA" & i).Text & ";" & wsMLOG.Range("AB" & i).Text
                        Mail.To = mailAddress
                        Mail.Subject = wsMLOG.Range("B" & i).Value & " " & wsMLOG.Range("C" & i).Value & " Claims"
                        Mail.Body = Sheets("Sheet1").Range("A1").Text

I stepped through the code and this is the value
"DeltaDTest001@Google.com;;;;DeltaDTest005@Google.com;;;DeltaDTest003@Google.com;;;;;DeltaDTest002@Google.com;;;DeltaDTest004@Google.com;"

Those emails are valid but it still came back undeliverable.

 

[SupremeDr]

I have some more information on this.

I changed my code to the following

mailAddress = wsMLOG.Range("L" & i).Value & ";" & wsMLOG.Range("M" & i).Value & ";" & wsMLOG.Range("N" & i).Text & ";" & wsMLOG.Range("O" & i).Text & ";" & wsMLOG.Range("P" & i).Text & ";" & wsMLOG.Range("Q" & i).Text & ";" & wsMLOG.Range("R" & i).Text & ";" & wsMLOG.Range("S" & i).Text & ";" & wsMLOG.Range("T" & i).Text & ";" & wsMLOG.Range("U" & i).Text & ";" & wsMLOG.Range("V" & i).Text & ";" & wsMLOG.Range("W" & i).Text & ";" & wsMLOG.Range("X" & i).Text & ";" & wsMLOG.Range("Y" & i).Text & ";" & wsMLOG.Range("Z" & i).Text & ";" & wsMLOG.Range("AA" & i).Text & ";" & wsMLOG.Range("AB" & i).Text
                        Mail.To = mailAddress
                        Mail.Subject = wsMLOG.Range("B" & i).Value & " " & wsMLOG.Range("C" & i).Value & " Claims"
                        Mail.Body = Sheets("Sheet1").Range("A1").Text

I stepped through the code and this is the value
"DeltaDTest001@Google.com;;;;DeltaDTest005@Google.com;;;DeltaDTest003@Google.com;;;;;DeltaDTest002@Google.com;;;DeltaDTest004@Google.com;"

Those emails are valid but it still came back undeliverable.

10/10 idiot here. Used google.com instead of gmail.com. It works now (I had to change .value to .text)