VBA email addresses dropping out with .To

P

PLLL

New Member
Joined
Mar 1, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I have VBA code within excel to email out a large number of statements from outlook, it works great for the first 20 or so statements but after this the To and Cc email addresses drop out from the emails for some reason. Has anyone experienced this before and know of anyway to fix it please? Extract of code below.

Thanks

Patrick


'Create the email
On Error Resume Next
With OutMail1
.Display
.To = Sheets("01. STATEMENT").Range("O" & row)
.Cc = Sheets("01. STATEMENT").Range("R" & row)
.Subject = Sheets("01. STATEMENT").Range("J" & row) & " " & Sheets("01. STATEMENT").Range("K" & row) & "Financial Report " & Sheets("01. STATEMENT").Range("C1")
.Body = ""
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Why is there an On Error Resume Next statement there? It's probably just hiding whatever the cause is.
 
Upvote 0
RoryA said:
Why is there an On Error Resume Next statement there? It's probably just hiding whatever the cause is.
Click to expand...
Thanks Rory, here is the whole code, I think I was having trouble getting some text above the actual statement and this way seemed to work - I am not fluent in vba, I tend to do plenty of googleing and trial and error :)


VBA Code: 
Sub Mail_Selection_Range_Outlook_Body(ByVal row As String)

'If the statement is active then continue or skip
 If Sheets("01. STATEMENT").Range("H" & row).Value = "YES" Then
   
    Dim OutApp1 As Object
    Dim OutMail1 As Object
    Dim wdDoc1 As Object
    Dim oRng1 As Object
      
    Set OutApp1 = CreateObject("Outlook.Application")
    Set OutMail1 = OutApp1.CreateItem(0)
   
'Set the name and file location of the excel attachment
Excelfile = ActiveWorkbook.FullName
  i = InStrRev(Excelfile, ".")
  If i > 1 Then Excelfile = Left(Excelfile, i - 1)
Excelfile = Environ("USERPROFILE") & "\OneDrive - .........................\Documents\" & Sheet1.Range("J" & row) & " " & Sheet1.Range("K" & row) & " Financial Report " & Format(Sheet1.Range("C1"), "mmm.yy") & ".xlsx"
      
'Create the excel attachment
    Range("A" & row - 5 & ":" & "E" & row + 4).Select
    Selection.Copy
    Workbooks.Add
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteFormats
    Selection.PasteSpecial Paste:=xlPasteColumnWidths
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.SaveAs Filename:=Excelfile
    ActiveWorkbook.Close

'Copy the statement to post into the email body
    Workbooks(".................statements V7.xlsm").Activate
   Sheets("01. STATEMENT").Range("A" & row - 5 & ":" & "E" & row + 4).Select
    Selection.Copy
   
'Create the email
    On Error Resume Next
    With OutMail1
        .Display
        .To = Sheets("01. STATEMENT").Range("O" & row)
        .Cc = Sheets("01. STATEMENT").Range("R" & row)
        .Subject = Sheets("01. STATEMENT").Range("J" & row) & " " & Sheets("01. STATEMENT").Range("K" & row) & "Financial Report " & Sheets("01. STATEMENT").Range("C1")
        .Body = ""
        .Attachments.Add Excelfile
           
'Delete the excel file from one drive
            Kill (Excelfile)
'Email body AFTER statement
         .htmlbody = "<br>" & "Please note..............................." & "<br>" & "<br>" & "Please contact ....................: " & Sheets("01. STATEMENT").Range("Q" & row) & "<br>" & "<br>" & " Please do not respond to this email as this mailbox is not monitored." & "<br>" & "<br>" & "Best Regards" & "<br>" & "<br>" & "..........................."
                Set olInsp = .GetInspector
            Set wdDoc1 = olInsp.WordEditor
            Set oRng1 = wdDoc1.Range
            oRng1.collapse 1
            oRng1.Paste
            For Each shp In wdDoc1.InlineShapes
            shp.ScaleHeight = 110
            shp.ScaleWidth = 110
            Next
'Email body BEFORE statement
         .htmlbody = "<font style='font-family:calibri;font-size:15.0'>" & "Dear " & Sheet1.Range("N" & row) & "," & "<br>" & "<br>" _
    & "Please find the...............e " & Sheets("01. STATEMENT").Range("K" & row) & " project below and attached." & "<br>" & "<br>" & .htmlbody


    End With
    On Error GoTo 0

    Set OutMail1 = Nothing
    Set OutApp1 = Nothing

End If

End Sub
 
Last edited by a moderator:
Upvote 0
Comment out the On Error Resume Next line then run the code again and see what happens.
 
Upvote 0
RoryA said:
Comment out the On Error Resume Next line then run the code again and see what happens.
Click to expand...

It seems to have worked!!! thank you so much! What was going wrong then do you think? was it getting to an email address it didnt like and then it was skipping through that step?
 
Upvote 0
Yes. Removing that line shouldn't fix it though! It should just allow you to debug it when the error happens.
 
Upvote 0
Solution
RoryA said:
Yes. Removing that line shouldn't fix it though! It should just allow you to debug it when the error happens.
Click to expand...

Hi Rory, I'm still having trouble with this unfortunately so wonder if you could have another look for me please? I have posted the whole code below - there are 2 subs. The bottom sub is the code to prepare the email statements and the top one repeats that code 400 odd times for different rows i.e. I have 400 statements stacked one on top of each other in an excel worksheet. The trouble I have now is that after about 30 statements it stops and so I have to comment out in blocks of 30 at a time. I will be handing this process over to someone else soon so I want it to work seamlessly. I think its just some sort of glitch so looking for some way around it, perhaps stopping and restarting after each block of 30 statements - can you think of anything please??


Code: 
Sub runmacro()


Dim response As VbMsgBoxResult

'You need to unlock the statements in cell D1
   If Sheets("01. STATEMENT").Range("D1").Value <> "SEND EMAILS UNLOCKED" Then
    response = MsgBox("Cell D1 must be set to 'SEND EMAILS UNLOCKED' to continue", vbOKOnly)
    If response = vbOK Then Exit Sub
    
    End If
'2 message boxes to give you a chance to cancel before sending out the emails
    response = MsgBox("You are about to send ~400 email statements, do you want to continue??", vbOKCancel + vbInformation)
    If response = vbCancel Then Exit Sub

    response = MsgBox("ARE YOU SURE??", vbOKCancel + vbInformation)
    If response = vbCancel Then Exit Sub
    
'Filter only on the budget categories with either expenditure or budget or both
'ActiveSheet.ShowAllData
'ActiveSheet.Range("$G$1:$R$5001").AutoFilter Field:=1, Criteria1:="YES"

'Set statements back to locked so they are locked next time you open the spreadsheet
Range("D1").Value = "SEND EMAILS LOCKED"

'Loop through the macro for all the seperate statements - "x" = row number of first budget category per statement

Mail_Selection_Range_Outlook_Body "7"
Mail_Selection_Range_Outlook_Body "17"
Mail_Selection_Range_Outlook_Body "27"
Mail_Selection_Range_Outlook_Body "37"
Mail_Selection_Range_Outlook_Body "47"
Mail_Selection_Range_Outlook_Body "57"
Mail_Selection_Range_Outlook_Body "67"
Mail_Selection_Range_Outlook_Body "77"
Mail_Selection_Range_Outlook_Body "87"
Mail_Selection_Range_Outlook_Body "97"
Mail_Selection_Range_Outlook_Body "107"
Mail_Selection_Range_Outlook_Body "117"
Mail_Selection_Range_Outlook_Body "127"
Mail_Selection_Range_Outlook_Body "137"
Mail_Selection_Range_Outlook_Body "147"
Mail_Selection_Range_Outlook_Body "157"
Mail_Selection_Range_Outlook_Body "167"
Mail_Selection_Range_Outlook_Body "177"
Mail_Selection_Range_Outlook_Body "187"
Mail_Selection_Range_Outlook_Body "197"
Mail_Selection_Range_Outlook_Body "207"
Mail_Selection_Range_Outlook_Body "217"
Mail_Selection_Range_Outlook_Body "227"
Mail_Selection_Range_Outlook_Body "237"
Mail_Selection_Range_Outlook_Body "247"
Mail_Selection_Range_Outlook_Body "257"
Mail_Selection_Range_Outlook_Body "267"
Mail_Selection_Range_Outlook_Body "277"
Mail_Selection_Range_Outlook_Body "287"
Mail_Selection_Range_Outlook_Body "297"
Mail_Selection_Range_Outlook_Body "307"
Mail_Selection_Range_Outlook_Body "317"
Mail_Selection_Range_Outlook_Body "327"
Mail_Selection_Range_Outlook_Body "337"
Mail_Selection_Range_Outlook_Body "347"
Mail_Selection_Range_Outlook_Body "357"
Mail_Selection_Range_Outlook_Body "367"
Mail_Selection_Range_Outlook_Body "377"
Mail_Selection_Range_Outlook_Body "387"
Mail_Selection_Range_Outlook_Body "397"
Mail_Selection_Range_Outlook_Body "407"
Mail_Selection_Range_Outlook_Body "417"
Mail_Selection_Range_Outlook_Body "427"
Mail_Selection_Range_Outlook_Body "437"
Mail_Selection_Range_Outlook_Body "447"
Mail_Selection_Range_Outlook_Body "457"
Mail_Selection_Range_Outlook_Body "467"
Mail_Selection_Range_Outlook_Body "477"
Mail_Selection_Range_Outlook_Body "487"
Mail_Selection_Range_Outlook_Body "497"
Mail_Selection_Range_Outlook_Body "507"
Mail_Selection_Range_Outlook_Body "517"
Mail_Selection_Range_Outlook_Body "527"
Mail_Selection_Range_Outlook_Body "537"
Mail_Selection_Range_Outlook_Body "547"
Mail_Selection_Range_Outlook_Body "557"
Mail_Selection_Range_Outlook_Body "567"
Mail_Selection_Range_Outlook_Body "577"
Mail_Selection_Range_Outlook_Body "587"
Mail_Selection_Range_Outlook_Body "597"
Mail_Selection_Range_Outlook_Body "607"
Mail_Selection_Range_Outlook_Body "617"
Mail_Selection_Range_Outlook_Body "627"
Mail_Selection_Range_Outlook_Body "637"
Mail_Selection_Range_Outlook_Body "647"
Mail_Selection_Range_Outlook_Body "657"
Mail_Selection_Range_Outlook_Body "667"
Mail_Selection_Range_Outlook_Body "677"
Mail_Selection_Range_Outlook_Body "687"
Mail_Selection_Range_Outlook_Body "697"
Mail_Selection_Range_Outlook_Body "707"
Mail_Selection_Range_Outlook_Body "717"
Mail_Selection_Range_Outlook_Body "727"
Mail_Selection_Range_Outlook_Body "737"
Mail_Selection_Range_Outlook_Body "747"
Mail_Selection_Range_Outlook_Body "757"
Mail_Selection_Range_Outlook_Body "767"
Mail_Selection_Range_Outlook_Body "777"
Mail_Selection_Range_Outlook_Body "787"
Mail_Selection_Range_Outlook_Body "797"
Mail_Selection_Range_Outlook_Body "807"
Mail_Selection_Range_Outlook_Body "817"
Mail_Selection_Range_Outlook_Body "827"
Mail_Selection_Range_Outlook_Body "837"
Mail_Selection_Range_Outlook_Body "847"
Mail_Selection_Range_Outlook_Body "857"
Mail_Selection_Range_Outlook_Body "867"
Mail_Selection_Range_Outlook_Body "877"
Mail_Selection_Range_Outlook_Body "887"
Mail_Selection_Range_Outlook_Body "897"
Mail_Selection_Range_Outlook_Body "907"
Mail_Selection_Range_Outlook_Body "917"
Mail_Selection_Range_Outlook_Body "927"
Mail_Selection_Range_Outlook_Body "937"
Mail_Selection_Range_Outlook_Body "947"
Mail_Selection_Range_Outlook_Body "957"
Mail_Selection_Range_Outlook_Body "967"
Mail_Selection_Range_Outlook_Body "977"
Mail_Selection_Range_Outlook_Body "987"
Mail_Selection_Range_Outlook_Body "997"
Mail_Selection_Range_Outlook_Body "1007"
Mail_Selection_Range_Outlook_Body "1017"
Mail_Selection_Range_Outlook_Body "1027"
Mail_Selection_Range_Outlook_Body "1037"
Mail_Selection_Range_Outlook_Body "1047"
Mail_Selection_Range_Outlook_Body "1057"
Mail_Selection_Range_Outlook_Body "1067"
Mail_Selection_Range_Outlook_Body "1077"
Mail_Selection_Range_Outlook_Body "1087"
Mail_Selection_Range_Outlook_Body "1097"
Mail_Selection_Range_Outlook_Body "1107"
Mail_Selection_Range_Outlook_Body "1117"
Mail_Selection_Range_Outlook_Body "1127"
Mail_Selection_Range_Outlook_Body "1137"
Mail_Selection_Range_Outlook_Body "1147"
Mail_Selection_Range_Outlook_Body "1157"
Mail_Selection_Range_Outlook_Body "1167"
Mail_Selection_Range_Outlook_Body "1177"
Mail_Selection_Range_Outlook_Body "1187"
Mail_Selection_Range_Outlook_Body "1197"
Mail_Selection_Range_Outlook_Body "1207"
Mail_Selection_Range_Outlook_Body "1217"
Mail_Selection_Range_Outlook_Body "1227"
Mail_Selection_Range_Outlook_Body "1237"
Mail_Selection_Range_Outlook_Body "1247"
Mail_Selection_Range_Outlook_Body "1257"
Mail_Selection_Range_Outlook_Body "1267"
Mail_Selection_Range_Outlook_Body "1277"
Mail_Selection_Range_Outlook_Body "1287"
Mail_Selection_Range_Outlook_Body "1297"
Mail_Selection_Range_Outlook_Body "1307"
Mail_Selection_Range_Outlook_Body "1317"
Mail_Selection_Range_Outlook_Body "1327"
Mail_Selection_Range_Outlook_Body "1337"
Mail_Selection_Range_Outlook_Body "1347"
Mail_Selection_Range_Outlook_Body "1357"
Mail_Selection_Range_Outlook_Body "1367"
Mail_Selection_Range_Outlook_Body "1377"
Mail_Selection_Range_Outlook_Body "1387"
Mail_Selection_Range_Outlook_Body "1397"
Mail_Selection_Range_Outlook_Body "1407"
Mail_Selection_Range_Outlook_Body "1417"
Mail_Selection_Range_Outlook_Body "1427"
Mail_Selection_Range_Outlook_Body "1437"
Mail_Selection_Range_Outlook_Body "1447"
Mail_Selection_Range_Outlook_Body "1457"
Mail_Selection_Range_Outlook_Body "1467"
Mail_Selection_Range_Outlook_Body "1477"
Mail_Selection_Range_Outlook_Body "1487"
Mail_Selection_Range_Outlook_Body "1497"
Mail_Selection_Range_Outlook_Body "1507"
Mail_Selection_Range_Outlook_Body "1517"
Mail_Selection_Range_Outlook_Body "1527"
Mail_Selection_Range_Outlook_Body "1537"
Mail_Selection_Range_Outlook_Body "1547"
Mail_Selection_Range_Outlook_Body "1557"
Mail_Selection_Range_Outlook_Body "1567"
Mail_Selection_Range_Outlook_Body "1577"
Mail_Selection_Range_Outlook_Body "1587"
Mail_Selection_Range_Outlook_Body "1597"
Mail_Selection_Range_Outlook_Body "1607"
Mail_Selection_Range_Outlook_Body "1617"
Mail_Selection_Range_Outlook_Body "1627"
Mail_Selection_Range_Outlook_Body "1637"
Mail_Selection_Range_Outlook_Body "1647"
Mail_Selection_Range_Outlook_Body "1657"
Mail_Selection_Range_Outlook_Body "1667"
Mail_Selection_Range_Outlook_Body "1677"
Mail_Selection_Range_Outlook_Body "1687"
Mail_Selection_Range_Outlook_Body "1697"
Mail_Selection_Range_Outlook_Body "1707"
Mail_Selection_Range_Outlook_Body "1717"
Mail_Selection_Range_Outlook_Body "1727"
Mail_Selection_Range_Outlook_Body "1737"
Mail_Selection_Range_Outlook_Body "1747"
Mail_Selection_Range_Outlook_Body "1757"
Mail_Selection_Range_Outlook_Body "1767"
Mail_Selection_Range_Outlook_Body "1777"
Mail_Selection_Range_Outlook_Body "1787"
Mail_Selection_Range_Outlook_Body "1797"
Mail_Selection_Range_Outlook_Body "1807"
Mail_Selection_Range_Outlook_Body "1817"
Mail_Selection_Range_Outlook_Body "1827"
Mail_Selection_Range_Outlook_Body "1837"
Mail_Selection_Range_Outlook_Body "1847"
Mail_Selection_Range_Outlook_Body "1857"
Mail_Selection_Range_Outlook_Body "1867"
Mail_Selection_Range_Outlook_Body "1877"
Mail_Selection_Range_Outlook_Body "1887"
Mail_Selection_Range_Outlook_Body "1897"
Mail_Selection_Range_Outlook_Body "1907"
Mail_Selection_Range_Outlook_Body "1917"
Mail_Selection_Range_Outlook_Body "1927"
Mail_Selection_Range_Outlook_Body "1937"
Mail_Selection_Range_Outlook_Body "1947"
Mail_Selection_Range_Outlook_Body "1957"
Mail_Selection_Range_Outlook_Body "1967"
Mail_Selection_Range_Outlook_Body "1977"
Mail_Selection_Range_Outlook_Body "1987"
Mail_Selection_Range_Outlook_Body "1997"
Mail_Selection_Range_Outlook_Body "2007"
Mail_Selection_Range_Outlook_Body "2017"
Mail_Selection_Range_Outlook_Body "2027"
Mail_Selection_Range_Outlook_Body "2037"
Mail_Selection_Range_Outlook_Body "2047"
Mail_Selection_Range_Outlook_Body "2057"
Mail_Selection_Range_Outlook_Body "2067"
Mail_Selection_Range_Outlook_Body "2077"
Mail_Selection_Range_Outlook_Body "2087"
Mail_Selection_Range_Outlook_Body "2097"
Mail_Selection_Range_Outlook_Body "2107"
Mail_Selection_Range_Outlook_Body "2117"
Mail_Selection_Range_Outlook_Body "2127"
Mail_Selection_Range_Outlook_Body "2137"
Mail_Selection_Range_Outlook_Body "2147"
Mail_Selection_Range_Outlook_Body "2157"
Mail_Selection_Range_Outlook_Body "2167"
Mail_Selection_Range_Outlook_Body "2177"
Mail_Selection_Range_Outlook_Body "2187"
Mail_Selection_Range_Outlook_Body "2197"
Mail_Selection_Range_Outlook_Body "2207"
Mail_Selection_Range_Outlook_Body "2217"
Mail_Selection_Range_Outlook_Body "2227"
Mail_Selection_Range_Outlook_Body "2237"
Mail_Selection_Range_Outlook_Body "2247"
Mail_Selection_Range_Outlook_Body "2257"
Mail_Selection_Range_Outlook_Body "2267"
Mail_Selection_Range_Outlook_Body "2277"
Mail_Selection_Range_Outlook_Body "2287"
Mail_Selection_Range_Outlook_Body "2297"
Mail_Selection_Range_Outlook_Body "2307"
Mail_Selection_Range_Outlook_Body "2317"
Mail_Selection_Range_Outlook_Body "2327"
Mail_Selection_Range_Outlook_Body "2337"
Mail_Selection_Range_Outlook_Body "2347"
Mail_Selection_Range_Outlook_Body "2357"
Mail_Selection_Range_Outlook_Body "2367"
Mail_Selection_Range_Outlook_Body "2377"
Mail_Selection_Range_Outlook_Body "2387"
Mail_Selection_Range_Outlook_Body "2397"
Mail_Selection_Range_Outlook_Body "2407"
Mail_Selection_Range_Outlook_Body "2417"
Mail_Selection_Range_Outlook_Body "2427"
Mail_Selection_Range_Outlook_Body "2437"
Mail_Selection_Range_Outlook_Body "2447"
Mail_Selection_Range_Outlook_Body "2457"
Mail_Selection_Range_Outlook_Body "2467"
Mail_Selection_Range_Outlook_Body "2477"
Mail_Selection_Range_Outlook_Body "2487"
Mail_Selection_Range_Outlook_Body "2497"
Mail_Selection_Range_Outlook_Body "2507"
Mail_Selection_Range_Outlook_Body "2517"
Mail_Selection_Range_Outlook_Body "2527"
Mail_Selection_Range_Outlook_Body "2537"
Mail_Selection_Range_Outlook_Body "2547"
Mail_Selection_Range_Outlook_Body "2557"
Mail_Selection_Range_Outlook_Body "2567"
Mail_Selection_Range_Outlook_Body "2577"
Mail_Selection_Range_Outlook_Body "2587"
Mail_Selection_Range_Outlook_Body "2597"
Mail_Selection_Range_Outlook_Body "2607"
Mail_Selection_Range_Outlook_Body "2617"
Mail_Selection_Range_Outlook_Body "2627"
Mail_Selection_Range_Outlook_Body "2637"
Mail_Selection_Range_Outlook_Body "2647"
Mail_Selection_Range_Outlook_Body "2657"
Mail_Selection_Range_Outlook_Body "2667"
Mail_Selection_Range_Outlook_Body "2677"
Mail_Selection_Range_Outlook_Body "2687"
Mail_Selection_Range_Outlook_Body "2697"
Mail_Selection_Range_Outlook_Body "2707"
Mail_Selection_Range_Outlook_Body "2717"
Mail_Selection_Range_Outlook_Body "2727"
Mail_Selection_Range_Outlook_Body "2737"
Mail_Selection_Range_Outlook_Body "2747"
Mail_Selection_Range_Outlook_Body "2757"
Mail_Selection_Range_Outlook_Body "2767"
Mail_Selection_Range_Outlook_Body "2777"
Mail_Selection_Range_Outlook_Body "2787"
Mail_Selection_Range_Outlook_Body "2797"
Mail_Selection_Range_Outlook_Body "2807"
Mail_Selection_Range_Outlook_Body "2817"
Mail_Selection_Range_Outlook_Body "2827"
Mail_Selection_Range_Outlook_Body "2837"
Mail_Selection_Range_Outlook_Body "2847"
Mail_Selection_Range_Outlook_Body "2857"
Mail_Selection_Range_Outlook_Body "2867"
Mail_Selection_Range_Outlook_Body "2877"
Mail_Selection_Range_Outlook_Body "2887"
Mail_Selection_Range_Outlook_Body "2897"
Mail_Selection_Range_Outlook_Body "2907"
Mail_Selection_Range_Outlook_Body "2917"
Mail_Selection_Range_Outlook_Body "2927"
Mail_Selection_Range_Outlook_Body "2937"
Mail_Selection_Range_Outlook_Body "2947"
Mail_Selection_Range_Outlook_Body "2957"
Mail_Selection_Range_Outlook_Body "2967"
Mail_Selection_Range_Outlook_Body "2977"
Mail_Selection_Range_Outlook_Body "2987"
Mail_Selection_Range_Outlook_Body "2997"
Mail_Selection_Range_Outlook_Body "3007"
Mail_Selection_Range_Outlook_Body "3017"
Mail_Selection_Range_Outlook_Body "3027"
Mail_Selection_Range_Outlook_Body "3037"
Mail_Selection_Range_Outlook_Body "3047"
Mail_Selection_Range_Outlook_Body "3057"
Mail_Selection_Range_Outlook_Body "3067"
Mail_Selection_Range_Outlook_Body "3077"
Mail_Selection_Range_Outlook_Body "3087"
Mail_Selection_Range_Outlook_Body "3097"
Mail_Selection_Range_Outlook_Body "3107"
Mail_Selection_Range_Outlook_Body "3117"
Mail_Selection_Range_Outlook_Body "3127"
Mail_Selection_Range_Outlook_Body "3137"
Mail_Selection_Range_Outlook_Body "3147"
Mail_Selection_Range_Outlook_Body "3157"
Mail_Selection_Range_Outlook_Body "3167"
Mail_Selection_Range_Outlook_Body "3177"
Mail_Selection_Range_Outlook_Body "3187"
Mail_Selection_Range_Outlook_Body "3197"
Mail_Selection_Range_Outlook_Body "3207"
Mail_Selection_Range_Outlook_Body "3217"
Mail_Selection_Range_Outlook_Body "3227"
Mail_Selection_Range_Outlook_Body "3237"
Mail_Selection_Range_Outlook_Body "3247"
Mail_Selection_Range_Outlook_Body "3257"
Mail_Selection_Range_Outlook_Body "3267"
Mail_Selection_Range_Outlook_Body "3277"
Mail_Selection_Range_Outlook_Body "3287"
Mail_Selection_Range_Outlook_Body "3297"
Mail_Selection_Range_Outlook_Body "3307"
Mail_Selection_Range_Outlook_Body "3317"
Mail_Selection_Range_Outlook_Body "3327"
Mail_Selection_Range_Outlook_Body "3337"
Mail_Selection_Range_Outlook_Body "3347"
Mail_Selection_Range_Outlook_Body "3357"
Mail_Selection_Range_Outlook_Body "3367"
Mail_Selection_Range_Outlook_Body "3377"
Mail_Selection_Range_Outlook_Body "3387"
Mail_Selection_Range_Outlook_Body "3397"
Mail_Selection_Range_Outlook_Body "3407"
Mail_Selection_Range_Outlook_Body "3417"
Mail_Selection_Range_Outlook_Body "3427"
Mail_Selection_Range_Outlook_Body "3437"
Mail_Selection_Range_Outlook_Body "3447"
Mail_Selection_Range_Outlook_Body "3457"
Mail_Selection_Range_Outlook_Body "3467"
Mail_Selection_Range_Outlook_Body "3477"
Mail_Selection_Range_Outlook_Body "3487"
Mail_Selection_Range_Outlook_Body "3497"
Mail_Selection_Range_Outlook_Body "3507"
Mail_Selection_Range_Outlook_Body "3517"
Mail_Selection_Range_Outlook_Body "3527"
Mail_Selection_Range_Outlook_Body "3537"
Mail_Selection_Range_Outlook_Body "3547"
Mail_Selection_Range_Outlook_Body "3557"
Mail_Selection_Range_Outlook_Body "3567"
Mail_Selection_Range_Outlook_Body "3577"
Mail_Selection_Range_Outlook_Body "3587"
Mail_Selection_Range_Outlook_Body "3597"
Mail_Selection_Range_Outlook_Body "3607"
Mail_Selection_Range_Outlook_Body "3617"
Mail_Selection_Range_Outlook_Body "3627"
Mail_Selection_Range_Outlook_Body "3637"
Mail_Selection_Range_Outlook_Body "3647"
Mail_Selection_Range_Outlook_Body "3657"
Mail_Selection_Range_Outlook_Body "3667"
Mail_Selection_Range_Outlook_Body "3677"
Mail_Selection_Range_Outlook_Body "3687"
Mail_Selection_Range_Outlook_Body "3697"
Mail_Selection_Range_Outlook_Body "3707"
Mail_Selection_Range_Outlook_Body "3717"
Mail_Selection_Range_Outlook_Body "3727"
Mail_Selection_Range_Outlook_Body "3737"
Mail_Selection_Range_Outlook_Body "3747"
Mail_Selection_Range_Outlook_Body "3757"
Mail_Selection_Range_Outlook_Body "3767"
Mail_Selection_Range_Outlook_Body "3777"
Mail_Selection_Range_Outlook_Body "3787"
Mail_Selection_Range_Outlook_Body "3797"
Mail_Selection_Range_Outlook_Body "3807"
Mail_Selection_Range_Outlook_Body "3817"
Mail_Selection_Range_Outlook_Body "3827"
Mail_Selection_Range_Outlook_Body "3837"
Mail_Selection_Range_Outlook_Body "3847"
Mail_Selection_Range_Outlook_Body "3857"
Mail_Selection_Range_Outlook_Body "3867"
Mail_Selection_Range_Outlook_Body "3877"
Mail_Selection_Range_Outlook_Body "3887"
Mail_Selection_Range_Outlook_Body "3897"
Mail_Selection_Range_Outlook_Body "3907"
Mail_Selection_Range_Outlook_Body "3917"
Mail_Selection_Range_Outlook_Body "3927"
Mail_Selection_Range_Outlook_Body "3937"
Mail_Selection_Range_Outlook_Body "3947"
Mail_Selection_Range_Outlook_Body "3957"
Mail_Selection_Range_Outlook_Body "3967"
Mail_Selection_Range_Outlook_Body "3977"
Mail_Selection_Range_Outlook_Body "3987"
Mail_Selection_Range_Outlook_Body "3997"
Mail_Selection_Range_Outlook_Body "4007"
Mail_Selection_Range_Outlook_Body "4017"
Mail_Selection_Range_Outlook_Body "4027"
Mail_Selection_Range_Outlook_Body "4037"
Mail_Selection_Range_Outlook_Body "4047"
Mail_Selection_Range_Outlook_Body "4057"
Mail_Selection_Range_Outlook_Body "4067"
Mail_Selection_Range_Outlook_Body "4077"
Mail_Selection_Range_Outlook_Body "4087"
Mail_Selection_Range_Outlook_Body "4097"
Mail_Selection_Range_Outlook_Body "4107"
Mail_Selection_Range_Outlook_Body "4117"
Mail_Selection_Range_Outlook_Body "4127"
Mail_Selection_Range_Outlook_Body "4137"
Mail_Selection_Range_Outlook_Body "4147"
Mail_Selection_Range_Outlook_Body "4157"
Mail_Selection_Range_Outlook_Body "4167"
Mail_Selection_Range_Outlook_Body "4177"
Mail_Selection_Range_Outlook_Body "4187"
Mail_Selection_Range_Outlook_Body "4197"
Mail_Selection_Range_Outlook_Body "4207"
Mail_Selection_Range_Outlook_Body "4217"
Mail_Selection_Range_Outlook_Body "4227"
Mail_Selection_Range_Outlook_Body "4237"
Mail_Selection_Range_Outlook_Body "4247"
Mail_Selection_Range_Outlook_Body "4257"
Mail_Selection_Range_Outlook_Body "4267"
Mail_Selection_Range_Outlook_Body "4277"
Mail_Selection_Range_Outlook_Body "4287"
Mail_Selection_Range_Outlook_Body "4297"
Mail_Selection_Range_Outlook_Body "4307"
Mail_Selection_Range_Outlook_Body "4317"
Mail_Selection_Range_Outlook_Body "4327"
Mail_Selection_Range_Outlook_Body "4337"
Mail_Selection_Range_Outlook_Body "4347"
Mail_Selection_Range_Outlook_Body "4357"
Mail_Selection_Range_Outlook_Body "4367"
Mail_Selection_Range_Outlook_Body "4377"
Mail_Selection_Range_Outlook_Body "4387"
Mail_Selection_Range_Outlook_Body "4397"
Mail_Selection_Range_Outlook_Body "4407"
Mail_Selection_Range_Outlook_Body "4417"
Mail_Selection_Range_Outlook_Body "4427"
Mail_Selection_Range_Outlook_Body "4437"
Mail_Selection_Range_Outlook_Body "4447"
Mail_Selection_Range_Outlook_Body "4457"
Mail_Selection_Range_Outlook_Body "4467"
Mail_Selection_Range_Outlook_Body "4477"
Mail_Selection_Range_Outlook_Body "4487"
Mail_Selection_Range_Outlook_Body "4497"
Mail_Selection_Range_Outlook_Body "4507"
Mail_Selection_Range_Outlook_Body "4517"
Mail_Selection_Range_Outlook_Body "4527"
Mail_Selection_Range_Outlook_Body "4537"
Mail_Selection_Range_Outlook_Body "4547"
Mail_Selection_Range_Outlook_Body "4557"
Mail_Selection_Range_Outlook_Body "4567"
Mail_Selection_Range_Outlook_Body "4577"
Mail_Selection_Range_Outlook_Body "4587"
Mail_Selection_Range_Outlook_Body "4597"
Mail_Selection_Range_Outlook_Body "4607"
Mail_Selection_Range_Outlook_Body "4617"
Mail_Selection_Range_Outlook_Body "4627"
Mail_Selection_Range_Outlook_Body "4637"
Mail_Selection_Range_Outlook_Body "4647"
Mail_Selection_Range_Outlook_Body "4657"
Mail_Selection_Range_Outlook_Body "4667"
Mail_Selection_Range_Outlook_Body "4677"
Mail_Selection_Range_Outlook_Body "4687"
Mail_Selection_Range_Outlook_Body "4697"
Mail_Selection_Range_Outlook_Body "4707"
Mail_Selection_Range_Outlook_Body "4717"
Mail_Selection_Range_Outlook_Body "4727"
Mail_Selection_Range_Outlook_Body "4737"
Mail_Selection_Range_Outlook_Body "4747"
Mail_Selection_Range_Outlook_Body "4757"
Mail_Selection_Range_Outlook_Body "4767"
Mail_Selection_Range_Outlook_Body "4777"
Mail_Selection_Range_Outlook_Body "4787"
Mail_Selection_Range_Outlook_Body "4797"
Mail_Selection_Range_Outlook_Body "4807"
Mail_Selection_Range_Outlook_Body "4817"
Mail_Selection_Range_Outlook_Body "4827"
Mail_Selection_Range_Outlook_Body "4837"
Mail_Selection_Range_Outlook_Body "4847"
Mail_Selection_Range_Outlook_Body "4857"
Mail_Selection_Range_Outlook_Body "4867"
Mail_Selection_Range_Outlook_Body "4877"
Mail_Selection_Range_Outlook_Body "4887"
Mail_Selection_Range_Outlook_Body "4897"
Mail_Selection_Range_Outlook_Body "4907"
Mail_Selection_Range_Outlook_Body "4917"
Mail_Selection_Range_Outlook_Body "4927"
Mail_Selection_Range_Outlook_Body "4937"
Mail_Selection_Range_Outlook_Body "4947"
Mail_Selection_Range_Outlook_Body "4957"
Mail_Selection_Range_Outlook_Body "4967"
Mail_Selection_Range_Outlook_Body "4977"
Mail_Selection_Range_Outlook_Body "4987"
Mail_Selection_Range_Outlook_Body "4997"




End Sub

Sub Mail_Selection_Range_Outlook_Body(ByVal row As String)

'If the statement is active then continue or skip
 If Sheets("01. STATEMENT").Range("H" & row).Value = "YES" Then
    
    Dim OutApp1 As Object
    Dim OutMail1 As Object
    Dim wdDoc1 As Object
    Dim oRng1 As Object
      
    Set OutApp1 = CreateObject("Outlook.Application")
    Set OutMail1 = OutApp1.CreateItem(0)
    
 'Copy the statement to post into the email body
    Workbooks("xxxxxxxxxxxx.xlsm").Activate
   Sheets("01. STATEMENT").Range("A" & row - 5 & ":" & "E" & row + 4).Select
    Selection.Copy
    
'Create the email
    With OutMail1
        .Display
        .To = Sheets("01. STATEMENT").Range("O" & row)
        .Cc = Sheets("01. STATEMENT").Range("R" & row)
        .Subject = Sheets("01. STATEMENT").Range("J" & row) & " " & Sheets("01. STATEMENT").Range("K" & row) & " xxxxxxx " & Sheets("01. STATEMENT").Range("C1")
        .Body = ""


End If
            
    'Email body AFTER statement
         .htmlbody = "<br>" & "xxxxxxxxx." & "<br>" & "<br>" & "xxxxxxxxxx " & Sheets("01. STATEMENT").Range("Q" & row) & "xxxxxxxxxxxx " & Sheets("01. STATEMENT").Range("Q" & row) & "<br>" & "<br>" & "xxxxxxxxxx" & "<br>" & "<br>" & "xxxxxxxxxxxxxxxx"
                Set olInsp = .GetInspector
            Set wdDoc1 = olInsp.WordEditor
            Set oRng1 = wdDoc1.Range
            oRng1.collapse 1
            oRng1.Paste
            For Each shp In wdDoc1.InlineShapes
            shp.ScaleHeight = 110
            shp.ScaleWidth = 110
            Next
'Email body BEFORE statement
         .htmlbody = "<font style='font-family:calibri;font-size:15.0'>" & "xxxxxxxxxxxx " & Sheet1.Range("N" & row) & "," & "<br>" & "<br>" _
    & "xxxxxxxxxxxx " & Sheets("01. STATEMENT").Range("K" & row) & " xxxxxxxxxxxxx " & Sheets("01. STATEMENT").Range("K" & row) & " xxxxxxxxx" & "<br>" & "<br>" & .htmlbody


    End With
    On Error GoTo 0

    Set OutMail1 = Nothing
    Set OutApp1 = Nothing

End If

End Sub
 
Upvote 0
That code shouldn't run at all as you have an End If line in the middle of a With...End With block.

Since you're using every 10th row, you don't need 400 calls, just use a loop:

VBA Code: 
Dim n As Long
For n = 7 To 4997 Step 10
   Mail_Selection_Range_Outlook_Body CStr(n)
Next n
 
Upvote 0
RoryA said:
That code shouldn't run at all as you have an End If line in the middle of a With...End With block.

Since you're using every 10th row, you don't need 400 calls, just use a loop:

VBA Code: 
Dim n As Long
For n = 7 To 4997 Step 10
   Mail_Selection_Range_Outlook_Body CStr(n)
Next n
Click to expand...
Thanks Rory, have sorted that but still stopping after about 30 - getting run time error 440 and then this when I debug:
1683799514581.png

It stops in a different place each time (always around 30) so its not as if its finding an email address it doesnt like
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
VBA to send multiple emails, run time error 440 after ~30 emails
Replies
5
Views
847
Domenic
D
J
  • Question
VBA code to send an email if a certain IF Criteria is met that loops when the spreadsheet is open
Replies
1
Views
242
joeyc123
J
Michael Fowler
  • Solved
VBA Code Sending Email to Multiple Recipients with Cell References instead of addresses
Replies
5
Views
497
6StringJazzer
6StringJazzer
T
  • Solved
Sending daily emails based on criteria, with email body corresponding to parallel cell which holds email address(es)
Replies
1
Views
775
twinwings
T
J
  • Solved
Automatically sending email from a shared mailbox via an Excel macro
Replies
1
Views
764
Jeff Leubecker
J

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,093
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top