I've some VBA which sends email via a list and includes attachments, but often instead of sending the 50 it should (code counts rows) it'll send less then less etc. I'm not sure it's the code itself, as sometime it works perfectly!
Any advice or pointers gratefully received.
Hi and welcome
Let me consult my crystal ball on this one.... I see posting some code and perhaps attaching a sample workbook in your near future....
Without seeing the problem in context it's a little difficult to even suggest what might be causing it.
It's like me telling you there's a light on the dashboard of my car that isn't normally there and asking you what could be causing it, without any further information.
BSB
Please see code below..
Sub Send_Multi_Email_Attach()
Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet
Dim StrBody As String
Dim Row_Count As Integer
Dim Col_Count As Integer
Dim i As Integer
Dim j As Integer
Set ws = ThisWorkbook.Sheets("Sheet1") '*************** change to match worksheets as required ***************
ws.Activate
Row_Count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown))) 'Check ranges for both counts if needed
Col_Count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
For i = 2 To Row_Count
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
StrBody = "<Body style = font-size:12pts, font-family:arial>" & _
"Dear " & ws.Cells(i, 8).Text & "," & _
"<BR><BR>Please find your ######## Report attached." & _
"<BR><BR>Regards<BR><BR>Mr #####<BR><BR>" & _
"##### #######"
'Update per reporting/emailing situ
On Error Resume Next
With OutMail
.To = ws.Cells(i, 1).Text
.CC = ""
.BCC = ""
.Subject = ws.Cells(i, 2).Text 'Update Workbook per reporting/emailing situ
.Sentonbehalfofname = "##################"
.Display
.HTMLBody = StrBody & .HTMLBody
For j = 3 To Col_Count
.attachments.Add ws.Cells(i, j).Text
Next j
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Next i
End Sub
Please note workbook/worksheet headings below, can't send actual workbook due to GDPR.
Recipient(s) Subject Attachment 1 Attachment 2 Attachment 3 Attachment 4 Attachment 5 Forenames
Thanks
Mick
Does this work any better?
Sub test() Dim OutApp As Object Dim ws As Worksheet Dim row_count As Long, col_count As Long, i As Long, j As Long Dim p As String, strbody As String Set ws = ThisWorkbook.Sheets("Sheet1") With ws row_count = .Cells(Rows.Count, 1).End(xlUp).Row col_count = .Cells(1, Columns.Count).End(xlToLeft).Column End With If row_count = 1 Then Exit Sub Set OutApp = CreateObject("Outlook.Application") For i = 2 To row_count strbody = "<Body style = font-size:12pts, font-family:arial>" & _ "Dear " & ws.Cells(i, 8).Text & "," & _ "<BR><BR>Please find your ######## Report attached." & _ "<BR><BR>Regards<BR><BR>Mr #####<BR><BR>" & _ "##### #######" With OutApp.createitem(0) .To = ws.Cells(i, 1).Value .Subject = ws.Cells(i, 2).Value .Sentonbehalfofname = "##################" .Display .HTMLBody = strbody & .HTMLBody For j = 3 To col_count p = ws.Cells(i, j).Value If Len(p) Then If Dir(p) <> "" Then .attachments.Add p End If Next End With DoEvents Next i Set OutApp = Nothing End Sub
Hi, many thanks for this. One issue, just tried it with 2 attachments and it only picked up the 2nd attachment?
Could you take a look and do you need file sending again?
Many thanks
Mick
File would be useful please.
Ignore last msg, found error was in path name.
Thanks again
Mick
You're welcome.
@M1ckyP
Next time you post code can you please place code tags around it for easier reading. TY
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Last edited by bakerman2; 01-14-2025 at 01:49 PM.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks