Add signature to e-mail; vba macro

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
Hello,

I have this code to automatically send mails.
Is there a way to add signature to these mails?
Code:
Public Sub mail() 
Dim MyAddress As String 
Dim olkApp As Outlook.Application 
Dim olkNameSpace As Outlook.Namespace 
Dim olkDefaultFolder As Outlook.MAPIFolder 
Dim olkMail As Outlook.MailItem 
If ActiveCell.Offset(, 13).Value = "Í/Á" Or ActiveCell.Offset(, 13).Value = "" Then MsgBox "Äåí õðÜñ÷åé äéåýèõíóç mail.", vbInformation, "Not valid mail address": Exit Sub 
On Error Resume Next 
Set olkApp = GetObject(, "Outlook.Application") 
If Err.Number <> 0 Then Set olkApp = CreateObject("Outlook.Application") 
On Error GoTo EH: 
Set olkNameSpace = olkApp.GetNamespace("MAPI") 
Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(Outlook.olFolderInbox) 
Set olkMail = olkDefaultFolder.Items.Add(Outlook.olMailItem) 
With olkMail 
    .To = ActiveCell.Offset(, 13).Text 
    .CC = "" 
    .BCC = "" 
    .Subject = "Test mail" 
    .Body = "message" 
    .Attachments.Add ("c:\info1.jpg") 
    .Attachments.Add ("c:\info2.jpg") 
    .Send 
    .Importance = olImportanceHigh 
End With 
EH: 
Set olkMail = Nothing 
Set olkDefaultFolder = Nothing 
Set olkNameSpace = Nothing 
Set olkApp = Nothing 
End Sub

Thank you
Kostas
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
Thank you Dominic,
I've given a try to rondebruin's codes at "add signature" link but for some reason it does not work. I'll post back the code for adding signature revised to suite me.
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
The code from rondebruin goes like:
Code:
Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    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
Sub Mail_Outlook_With_Signature_Html()
'Note: This macro use the Function GetBoiler
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)

    SigString = "C:\Documents and Settings\kostas\Application Data\Microsoft\Signatures\kostas.htm"
    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If
    With OutMail
        .To = ActiveCell.Text
        .CC = ""
        .BCC = ""
        .Subject = "test mail"
        .Body = "message"
        '.Attachments.Add ("c:\info1.jpg")
        '.Attachments.Add ("c:\info2.jpg")
        '.Send
        .Display
        .Importance = olImportanceHigh
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

It' does not add the signature "kostas.htm"
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297

ADVERTISEMENT

checking C:\Documents and Settings\kostas\Application Data\Microsoft\Signatures\ ,
where htm, txt, and rtf should exists, i only found kostas.htm to signatures folder, and in the subfolder kostas_files: filelist.xml, my image png and my image jpeg.
There should be a text, an htm, and an rtf file according to ron de bruin's advices. Maybe this is all the trouble, but still it uses kostas.htm that already exists...
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi ktab

Unfortunately, I can't help you any further on Outlook as I use Lotus Notes. I just thought I'd point you to Ron de Bruin's pages and all would be well. However, if you're still struggling, perhaps you could try a repost (and if I spot it, I'll keep my fingers to myself :) )

DominicB
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297

ADVERTISEMENT

Dominic, you have helped me so many times that i wish you don't keep your fingers to yourself. As for signature, i guess it's something i did wrong; i'll find that.

Thank you for all the assistance, see you at forum
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
kostas

Did you actually use the code from the site?

Specifically these parts.

For a text signature.
Code:
.Body = strbody & vbNewLine & vbNewLine & Signature
For an HTML signature.
Code:
.HTMLBody = strbody & "

" & Signature
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
Norie you are absolutelly right....
I used txt signature for html message. I thing i'll bump my head to the wall.

Thank you
 

Forum statistics

Threads
1,136,876
Messages
5,678,288
Members
419,753
Latest member
Sallylwy

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