Email Macro

[Littlemalky]

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.

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

 

[Joe4]

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]

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]

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]

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]

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]

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]

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.