dwilson38550m
Board Regular
- Joined
- Nov 21, 2005
- Messages
- 89
Hi,
I have been struggling with this and would be grateful for any suggestions. I have an email macro which runs down a list of recipients in column B and attaches a specified file in column C (saved to my desktop). This operates fine however the recipients have asked for one email with multiple attachments - ie XYZ (xyz@yahoo.co.uk) doesn't want to receive 6 separate emails each with a different attachment - instead they want to receive 1 email with 6 attachments on it (each a different file); 123 wants to receive one email (123@yahoo.co.uk) with 5 attachments (each a different file) etc.
I have attached below the macro script I use (it just needs adjusted a little). This is just an example but in practice I am sending out 300 statements so need to try to find a workable solution.
Any help would be much appreciated.
Thanks,
David
EXCEL COLUMNS :
A B C
jo.bloggs@yahoo.co.uk C:\desktop\Jo Bloggs Statement 28th Feb 2018.xls
abc@yahoo.co.uk C:\desktop\abc Statement 28th Feb 2018.xls
def@yahoo.co.uk C:\desktop\def Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 1 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 2 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 3 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 4 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 5 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 6 Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 a Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 b Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 c Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 d Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 e Statement 28th Feb 2018.xls
456@yahoo.co.uk C:\desktop\456 A Statement 28th Feb 2018.xls
I have been struggling with this and would be grateful for any suggestions. I have an email macro which runs down a list of recipients in column B and attaches a specified file in column C (saved to my desktop). This operates fine however the recipients have asked for one email with multiple attachments - ie XYZ (xyz@yahoo.co.uk) doesn't want to receive 6 separate emails each with a different attachment - instead they want to receive 1 email with 6 attachments on it (each a different file); 123 wants to receive one email (123@yahoo.co.uk) with 5 attachments (each a different file) etc.
I have attached below the macro script I use (it just needs adjusted a little). This is just an example but in practice I am sending out 300 statements so need to try to find a workable solution.
Any help would be much appreciated.
Thanks,
David
Code:
Sub emailgood()
'
' emailgood Macro
'
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("Sheet1")
Set OutApp = CreateObject("Outlook.Application")
For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = cell.Value
.Subject = "CUSTOMER STATEMENT as at 28 February 2018"
.Body = "Dear Sir/Madam, " & vbLf & vbLf & "Please find attached your monthly statement showing your current balance and any outstanding invoices as at 28th February. " & vbLf & vbLf & "Kind regards," & vbLf & vbLf & "David" & vbLf & vbLf & "Credit Controller" & cell.Offset(0, -1).Value
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
.Send
End With
Set OutMail = Nothing
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
EXCEL COLUMNS :
A B C
jo.bloggs@yahoo.co.uk C:\desktop\Jo Bloggs Statement 28th Feb 2018.xls
abc@yahoo.co.uk C:\desktop\abc Statement 28th Feb 2018.xls
def@yahoo.co.uk C:\desktop\def Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 1 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 2 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 3 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 4 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 5 Statement 28th Feb 2018.xls
xyz@yahoo.co.uk C:\desktop\xyz 6 Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 a Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 b Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 c Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 d Statement 28th Feb 2018.xls
123@yahoo.co.uk C:\desktop\123 e Statement 28th Feb 2018.xls
456@yahoo.co.uk C:\desktop\456 A Statement 28th Feb 2018.xls
Last edited by a moderator: