Send an email with signature in VBA-CodePudding

I try to ask my questions here 'cause I don't find any answer to my problems anywhere.

I try to send an email in vba from my excel using outlook signature but I get a 62 error.

This is the code I used, that works fine in another Excel worksheet and works fine in it, but not in my other worksheet.

Dim FSO As Scripting.FileSystemObject
Dim TextStream As Scripting.TextStream
Dim file_name As String
dim signature_name as string        
dim signature as string

Set FSO = Nothing
Set TextStream = Nothing
Set FSO = New Scripting.FileSystemObject
file_name = Environ("APPDATA") & "\Microsoft\Signatures\" & signature_name & ".htm"
Set TextStream = FSO.OpenTextFile(file_name, ForReading, False, TristateMixed)
                        
If Err.Num = 0 Then
    signature = TextStream.ReadAll
    signature = Replace(signature, signature_name & "_files/", Environ("APPDATA") & "\Microsoft\Signatures\" & signature_name & "_files/")
End if

I checked with checkpoint what file_name contained and it contained the right path to the signature files.

But still, when I try to OpenTextFile, I get an atEndOfLine = true and atEndOfStream = true

I hope you guys could help me 'cause I get stuck since two weeks on this problem :D

Thanks for your time

I try checkpoint, debugging, différents signature, copy/paste several codes

CodePudding user response：

At this point:

file_name = Environ("APPDATA") & "\Microsoft\Signatures\" & signature_name & ".htm"

signature_name is blank.

CodePudding user response：

I was having trouble getting my signature from "\Microsoft\Signatures\" or whatever address I was using. Something to do with the server I was on.

Anyway, I found this solution and it's been working for me consistently (as long as it's only your "default" signature you want:

Sub Build_And_Send_Email()

    Dim objOutlook As Object
    Dim objEmail As Object
    Dim objAttachment As Object

    `...

        'Setup Email
    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmail = objOutlook.CreateItem(olMailItem)

        'when displayed with no content, it inputs default signature
    objEmail.Display

    Set objAttachment = objEmail.Attachments
    objAttachment.Add "S:\Attachment Directory.pdf"
    eSubject = " My Subject "
    eBody = " Some text using html control for stuff like <br> line breaks "
    
        'Build Email
    With objEmail
        .To = eRecipient
        .CC = ""
        .BCC = ""
        .Subject = eSubject
            ' > Signature already included in .HTMLBody
        .HTMLBody = eBody & .HTMLBody
        .BodyFormat = olFormatHTML ' send plain text message
        '.Display
        '.Send
    End With
    
End Sub

I use this everyday for multiple reports. Seems to work on multiple computers. I hope it works for you.