email multiple records with multiple attachments in single mail

[newbielearning]

Hello Everyone,

i am trying to send emails to recipients with attachments but one recipient has multiple records and will have multiple attachments and i want macro to send just one mail consolidating all the rows and attachments.
So far, the macro is able to send single email for every row. i still can figure out how to add attachments since i have paths stored in cells. the code fails at adding attachments as well.

Colmun 7 has client names
Column 8 has email addresses
Column 9 has attachments(with full paths)

I want 1 email per client with multiple attachments going through the list.

Code:
Dim olapp As Outlook.Application
Dim olmail As Outlook.MailItem
Dim olinsp As Outlook.Inspector
Dim x As Range
Dim attach As Outlook.Attachment



Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set olapp = Outlook.Application

For Each x In ActiveSheet.Columns(8).Cells
attach = ActiveSheet.Range(x, 9).Value
Set olmail = olapp.CreateItem(olMailItem)

With olmail

.BodyFormat = olFormatHTML
.To = ActiveSheet.Range(x, 8).Value
.Subject = "Loan Dues"
.Attachments.Add "attach"
.Display

End With
Next x
End Sub


Any help is appreciated.

tks

 

[newbielearning]

OK, updating my post. i have got attachment part working. but its single mail per row per attachment.
How do i consolidate attachments and mail for every unique recipient?

Thks.

Hello Everyone,

i am trying to send emails to recipients with attachments but one recipient has multiple records and will have multiple attachments and i want macro to send just one mail consolidating all the rows and attachments.
So far, the macro is able to send single email for every row. i still can figure out how to add attachments since i have paths stored in cells. the code fails at adding attachments as well.

Colmun 7 has client names
Column 8 has email addresses
Column 9 has attachments(with full paths)

I want 1 email per client with multiple attachments going through the list.

Code:
Dim olapp As Outlook.Application
Dim olmail As Outlook.MailItem
Dim olinsp As Outlook.Inspector
Dim x As Range
Dim attach As Outlook.Attachment



Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set olapp = Outlook.Application

For Each x In ActiveSheet.Columns(8).Cells
attach = ActiveSheet.Range(x, 9).Value
Set olmail = olapp.CreateItem(olMailItem)

With olmail

.BodyFormat = olFormatHTML
.To = ActiveSheet.Range(x, 8).Value
.Subject = "Loan Dues"
.Attachments.Add "attach"
.Display

End With
Next x
End Sub


Any help is appreciated.

tks