Excel vba to auto-send customer emails (duplicate emails issue)

[nadz84]

Good day
The code below generates a unique email + attachment to each line in column A based on other columns.
This means if the same email exists more than once, they will receive more than one email with a unique attachment with each email.
What I would like it to do is check whether the email exists in more than one line (already sorted) and send them one email only instead (with all the attachments). is this possible?
this is my code:


<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim objOutlook As Outlook.Application
Dim objMail As Outlook.MailItem

Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range
Dim rngAttach As Range

ActiveSheet.Range("A2").Select

DoUntil IsEmpty(ActiveCell)

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItemFromTemplate("C:\Desktop\EBILL\template.oft")
With objMail
.To= ActiveCell.Offset(0,4).Value
.Subject ="Invoice For: "&" "& Month &" - "& Year
.Attachments.Add ActiveCell.Offset(0,5).Value
ActiveCell.Offset(1,0).Select
.Display 'Instead of .Display, you can use .Send to send the email or .Save to save a copy in the drafts folder
EndWith

Loop


Set objOutlook =Nothing
Set objMail =Nothing
Set rngTo =Nothing
Set rngSubject =Nothing
Set rngBody =Nothing
Set rngAttach =Nothing

</code>

 

[nadz84]

Hi guys. Someone helped me on another forum. Here is the answer in case it can be useful for anyone else with a similar problem:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">With objMail
.To = ActiveCell.Offset(0, 4).Value
.Subject = "Invoice For: " & " " & Month & " - " & Year
.Attachments.Add ActiveCell.Offset(0, 5).Value
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Offset(0, 4).Value <> .To
.Attachments.Add ActiveCell.Offset(0, 5).Value
ActiveCell.Offset(1, 0).Select
Loop
.Display 'Instead of .Display, you can use .Send to send the email or .Save to save a copy in the drafts folder
End With</code>