Sending email with macro

ScottyWm

Board Regular
Joined
Jan 10, 2004
Messages
105
This code used to work with previous XP and Office versions. Now it does not create an email as it should. I've searched and it looks to me that the code is all still correct. And the Macro does not throw any error, it just doesn't make the email. It just goes merrily along like it wasn't even there. Any help?
Code:
Sub Send_Emails()
'Last macro that makes the emails and summary email
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim supplier_name
    Dim supplier_email
    Dim signed

    Sheets("Scorecard Data").Select
        
    For Each Worksheet_Var In ActiveWorkbook.Worksheets
        ActiveSheet.Next.Select
            If ActiveSheet.Name = "Summary" Then
                GoTo 9:
            End If
        supplier_name = Range("c4").Value
        supplier_email = Range("j5").Value
        ActiveSheet.Select
        ActiveSheet.Copy
        Application.DisplayAlerts = False

        ActiveWorkbook.SaveAs Filename:= _
            Environ("userprofile") & "\documents\" & "Scorecard", _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, ConflictResolution:=2
        
         ActiveWorkbook.Close SaveChanges:=False
        Application.DisplayAlerts = True
    
   ' Make the Emails
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
        
            strbody = "email message body"
            signed = "signature"
     
            On Error Resume Next
            With OutMail
                .To = supplier_email
                .CC = ""
                .BCC = ""
                .Subject = supplier_name & ", Supplier Scorecard - " & Date
                .Body = strbody & signed
          'You can add a file like this
                .Attachments.Add (Environ("userprofile") & "\documents\" & "Scorecard")
                .Display   'use .Display or .Send
            End With
            On Error GoTo 0
        
            Set OutMail = Nothing
            Set OutApp = Nothing
            
    Next
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi there. As a start, try commenting out the on error resume next and see where its failing. You could also try .Send instead of .Display
 
Upvote 0
Wow, I feel foolish. All I needed to do to have it work was add the ".xlsx" file extension to the .Attachments.Add filename. Works fine now.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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