Email Macro

Littlemalky

Board Regular
Joined
Jan 14, 2011
Messages
223
Hi, I've been using this code in my daily report for ages. All of a sudden, it's no longer generating an email. I feel like there was an Office update or something that could have triggered this? Can anyone determine why this is no longer generating an email? I'm using Office 365.
VBA Code:
Public Sub Mail_Selection_Range_Outlook_Body()
' Working in Office 2000-2010

    Dim OutApp As Object
    Dim OutMail As Object
    Dim SigString As String
    Dim Signature As String
    Dim strbody As String

    On Error Resume Next

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    If ActiveWorkbook.Path <> "" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)

        strbody = "<font size=""3"" face=""Calibri"">" & _
                    "Good Morning Team,<br><br><br><br>"
    End If
    
    SigString = "C:\Users\" & Environ("username") & _
                "\AppData\Roaming\Microsoft\Signatures\Sig.htm"
    
     If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
        
    Else
        Signature = ""
    End If
    
    With OutMail
        .To = "John_Doe@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "CCVAS U.S. Daily Sales Report - " & Worksheets("Summary").Range("D4")
        .Attachments.Add "\\Blah\Daily Net Sales\" & Format(Date - 3, "yyyy") & "\Q" & DatePart("q", Date - 3) & "\" & _
                            Format(Date - 3, "MM") & "\CCVAS US Daily Sales Report.xlsb", olByValue, 1
        .HTMLBody = strbody & Signature
        '& "width='600' height='800'><br>"
        '& "width='1200' height='700'><br>
        '.HTMLBody = RangetoHTML(rng) & Signature
        '.Send
        'or use
        .Display
    End With

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

Function GetBoiler(ByVal sFile As String) As String
        Dim fso As Object
        Dim ts As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
        GetBoiler = ts.ReadAll
        ts.Close
End Function
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
A few things you can check:

- From the VB Editor, if you go to Tools -> References, do you see any checked references marked "MISSING"?

- Make sure that you place the line "Option Explicit" at the very top of your code (above the Public Sub line) . Then From the VB Editor, go to Debug -> Compile VBAProject. Does that find any errors?

- Browse to: "C:\Users\" & Environ("username")\AppData\Roaming\Microsoft\Signatures\Sig.htm"
Can you still find find that path/file (sometimes Windows like to change those paths with updates)?
 

Littlemalky

Board Regular
Joined
Jan 14, 2011
Messages
223
Thanks for the response Joe. Nothing says missing in my references. I added Option Explicit, there is nothing to debug. Also, the signature path is the same but it should just ignore that part if it wasn't. It's still not generating an email. I'm very confused.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Hmmm...

Also check to the file path in your attachment to make sure that is valid and you still have access to that.

My only other suggestions would be to try stepping through your code line-by-line (using the F8 key) where you can watch and see what happens, and hover over variables and see there values are at points in time. You can also see if it seems to exit at an odd time.

You may also want to try to see if it works on someone else's computer.
 

Littlemalky

Board Regular
Joined
Jan 14, 2011
Messages
223

ADVERTISEMENT

Yeah I did walk through it and it doesn't jump to exit at any point. It steps through each line of the code and still doesn't generate anything. It's also not working on other people's computers.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Dumb question, but I suppose I should ask, you are changing it to a valid email address, right?

Typically, when I encounter things that worked for a long time that stopped working for no known reason, I usually stop and asked, "what changed recently?" and start there.
Sometimes, its something like changes to data inputs, drive mappings, security settings, access controls, software updates, Office updates, etc.

Unfortunately, we are not on your network or in your environment, so if nothing in your VBA code changed, I don't know how much help we are going to be able to provide you on this (unless someone just happened to experience a similar issue).
 

Littlemalky

Board Regular
Joined
Jan 14, 2011
Messages
223
I appreciate all of your inputs, Joe. Yes, I cleared the actual email addresses just to paste here for security reasons. I may need to look at other techniques for generating an email since this is baffling me.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
I appreciate all of your inputs, Joe. Yes, I cleared the actual email addresses just to paste here for security reasons. I may need to look at other techniques for generating an email since this is baffling me.
I was pretty sure that was the case, but just needed to mention it, just in case.
I know I have had brain cramps in which I have overlooked the obvious and pulled some doozies like that (usually, when I am looking at a problem for too long, and start to lose the forest for the trees!).

Unfortunately, sometimes situations like these can be really difficult to debug without having access to your environment.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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
Top