Sending mails from Excel 2003 - Outlook signature

T

thomas

Hello,

Few months ago i asked ho to insert the outlook 2003 default signature to a
mail sent from excel vba

I was advised to use ".GetInspector" but i still cannot make it work.

I get an error message saying "read only property"


My code is :


Sub EnvoiMailMéthodeOLE(Adresse As String, Objet As String, Corps As String,
Optional Pièce As String, Optional Cc As String, Optional Bcc As String)

Dim MonAppliOutlook As New Outlook.Application
Dim MonMail As Outlook.MailItem
Dim MaPièce As Outlook.Attachments

Set MonMail = MonAppliOutlook.CreateItem(olMailItem)
MonAppliOutlook = MonMail.GetInspector

With MonMail
.Display '
.To = Adresse
If Not IsNull(Cc) Then .Cc = Cc
If Not IsNull(Bcc) Then .Bcc = Bcc
.Subject = Objet
.Body = Corps
If Not IsNull(Pièce) Then
Set MaPièce = .Attachments
MaPièce.Add Pièce, olByValue
End If
.Send
End With

End Sub

Thanks for you help
 
M

Michael Bauer [MVP - Outlook]

GetInspector returns an Inspector object, not an Application object. And
because it returns an object you'd need to use the Set statement. But
because you display the e-mail calling GetInspector shouldn't be necessary
at all.

After you've called Display, the Body property should contain the sig. That
is, calling .Body=Corps will overwerite the Body with the content of Corps.
If you want to preserve the content of Body and insert Corps at the top, you
need to do this:
..Body=Corps & .Body

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>



Am Thu, 18 Dec 2008 00:02:05 +0100 schrieb thomas:
 
T

Todd SChram - SCWA

I had the same issue with losing my signature when sending code from Access.
I tried using your suggestion of:

..Body=txtNote & .Body

but did not successfully maintain my default signature in the new email. If
I don't define the .Body property the signature will come up fine.

Thanks.

Todd
 
T

thomas

Thanks

I changed my code and I tried this :

#########################
Private Function EnvoiMail_HTML_New(Adresse As String, Objet As String,
Corps As String, Optional Pièce As String, Optional Cc As String, Optional
Bcc As String, Optional Envoyer_Mail As Boolean)

Dim MonAppliOutlook As Outlook.Application
Dim MonMail As Outlook.MailItem
Dim MaPièce As Outlook.Attachments

Set MonAppliOutlook = CreateObject("Outlook.Application")
Set MonMail = MonAppliOutlook.CreateItem(olMailItem)

On Error Resume Next

With MonMail

If Affichage = "OUI" Then .Display
.To = Adresse
If Not IsNull(Cc) Then .Cc = Cc
If Not IsNull(Bcc) Then .Bcc = Bcc
.BodyFormat = olFormatHTML
.HTMLBody = Corps & .HTMLBody


If Envoyer_Mail = True Then .Send

End With

Set MonAppliOutlook = Nothing
Set MonMail = Nothing

End Function
###########################


but with " .HTMLBody = Corps & .HTMLBody " i only get the signature
in the mail

and if i write " .HTMLBody = Corps " i only get the content of Corps

What did i do wrong?

Many thanks for your help

"Michael Bauer [MVP - Outlook]" <[email protected]> a écrit dans le message de
groupe de discussion : [email protected]...

GetInspector returns an Inspector object, not an Application object. And
because it returns an object you'd need to use the Set statement. But
because you display the e-mail calling GetInspector shouldn't be necessary
at all.

After you've called Display, the Body property should contain the sig. That
is, calling .Body=Corps will overwerite the Body with the content of Corps.
If you want to preserve the content of Body and insert Corps at the top, you
need to do this:
..Body=Corps & .Body

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>



Am Thu, 18 Dec 2008 00:02:05 +0100 schrieb thomas:
 
T

thomas

What do you think?

As said in previous post, whatever i write

..Body=Corps & .Body
or
.HTMLBody = Corps & .HTMLBody

it does not work


Thanks


"thomas" <nomail> a écrit dans le message de groupe de discussion :
#[email protected]...
Thanks

I changed my code and I tried this :

#########################
Private Function EnvoiMail_HTML_New(Adresse As String, Objet As String,
Corps As String, Optional Pièce As String, Optional Cc As String, Optional
Bcc As String, Optional Envoyer_Mail As Boolean)

Dim MonAppliOutlook As Outlook.Application
Dim MonMail As Outlook.MailItem
Dim MaPièce As Outlook.Attachments

Set MonAppliOutlook = CreateObject("Outlook.Application")
Set MonMail = MonAppliOutlook.CreateItem(olMailItem)

On Error Resume Next

With MonMail

If Affichage = "OUI" Then .Display
.To = Adresse
If Not IsNull(Cc) Then .Cc = Cc
If Not IsNull(Bcc) Then .Bcc = Bcc
.BodyFormat = olFormatHTML
.HTMLBody = Corps & .HTMLBody


If Envoyer_Mail = True Then .Send

End With

Set MonAppliOutlook = Nothing
Set MonMail = Nothing

End Function
###########################


but with " .HTMLBody = Corps & .HTMLBody " i only get the signature
in the mail

and if i write " .HTMLBody = Corps " i only get the content of Corps

What did i do wrong?

Many thanks for your help

"Michael Bauer [MVP - Outlook]" <[email protected]> a écrit dans le message de
groupe de discussion : [email protected]...

GetInspector returns an Inspector object, not an Application object. And
because it returns an object you'd need to use the Set statement. But
because you display the e-mail calling GetInspector shouldn't be necessary
at all.

After you've called Display, the Body property should contain the sig. That
is, calling .Body=Corps will overwerite the Body with the content of Corps.
If you want to preserve the content of Body and insert Corps at the top, you
need to do this:
..Body=Corps & .Body

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>



Am Thu, 18 Dec 2008 00:02:05 +0100 schrieb thomas:
 
T

thomas

no hint on what i did wrong?

Thanks

"thomas" <nomail> a écrit dans le message de groupe de discussion :
#[email protected]...
Thanks

I changed my code and I tried this :

#########################
Private Function EnvoiMail_HTML_New(Adresse As String, Objet As String,
Corps As String, Optional Pièce As String, Optional Cc As String, Optional
Bcc As String, Optional Envoyer_Mail As Boolean)

Dim MonAppliOutlook As Outlook.Application
Dim MonMail As Outlook.MailItem
Dim MaPièce As Outlook.Attachments

Set MonAppliOutlook = CreateObject("Outlook.Application")
Set MonMail = MonAppliOutlook.CreateItem(olMailItem)

On Error Resume Next

With MonMail

If Affichage = "OUI" Then .Display
.To = Adresse
If Not IsNull(Cc) Then .Cc = Cc
If Not IsNull(Bcc) Then .Bcc = Bcc
.BodyFormat = olFormatHTML
.HTMLBody = Corps & .HTMLBody


If Envoyer_Mail = True Then .Send

End With

Set MonAppliOutlook = Nothing
Set MonMail = Nothing

End Function
###########################


but with " .HTMLBody = Corps & .HTMLBody " i only get the signature
in the mail

and if i write " .HTMLBody = Corps " i only get the content of Corps

What did i do wrong?

Many thanks for your help

"Michael Bauer [MVP - Outlook]" <[email protected]> a écrit dans le message de
groupe de discussion : [email protected]...

GetInspector returns an Inspector object, not an Application object. And
because it returns an object you'd need to use the Set statement. But
because you display the e-mail calling GetInspector shouldn't be necessary
at all.

After you've called Display, the Body property should contain the sig. That
is, calling .Body=Corps will overwerite the Body with the content of Corps.
If you want to preserve the content of Body and insert Corps at the top, you
need to do this:
..Body=Corps & .Body

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>



Am Thu, 18 Dec 2008 00:02:05 +0100 schrieb thomas:
 
T

thomas

I found out

with the code .HTMLBody = Corps & .HTMLBody

i get an extra security message http://cjoint.com/data/mwwyjIyRHB.htm

if i answer OUI (YES) th signature is added

This secutity message is not very convenient.

Is there a way to avoid it with a permanent "YES" answer ?

Thanks


"thomas" <nomail> a écrit dans le message de groupe de discussion :
#[email protected]...
Thanks

I changed my code and I tried this :

#########################
Private Function EnvoiMail_HTML_New(Adresse As String, Objet As String,
Corps As String, Optional Pièce As String, Optional Cc As String, Optional
Bcc As String, Optional Envoyer_Mail As Boolean)

Dim MonAppliOutlook As Outlook.Application
Dim MonMail As Outlook.MailItem
Dim MaPièce As Outlook.Attachments

Set MonAppliOutlook = CreateObject("Outlook.Application")
Set MonMail = MonAppliOutlook.CreateItem(olMailItem)

On Error Resume Next

With MonMail

If Affichage = "OUI" Then .Display
.To = Adresse
If Not IsNull(Cc) Then .Cc = Cc
If Not IsNull(Bcc) Then .Bcc = Bcc
.BodyFormat = olFormatHTML
.HTMLBody = Corps & .HTMLBody


If Envoyer_Mail = True Then .Send

End With

Set MonAppliOutlook = Nothing
Set MonMail = Nothing

End Function
###########################


but with " .HTMLBody = Corps & .HTMLBody " i only get the signature
in the mail

and if i write " .HTMLBody = Corps " i only get the content of Corps

What did i do wrong?

Many thanks for your help

"Michael Bauer [MVP - Outlook]" <[email protected]> a écrit dans le message de
groupe de discussion : [email protected]...

GetInspector returns an Inspector object, not an Application object. And
because it returns an object you'd need to use the Set statement. But
because you display the e-mail calling GetInspector shouldn't be necessary
at all.

After you've called Display, the Body property should contain the sig. That
is, calling .Body=Corps will overwerite the Body with the content of Corps.
If you want to preserve the content of Body and insert Corps at the top, you
need to do this:
..Body=Corps & .Body

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>



Am Thu, 18 Dec 2008 00:02:05 +0100 schrieb thomas:
 
M

Micky

Joined
Jan 15, 2009
Messages
2
Reaction score
0
Sending mail from access 2007

Hi,

I had the same problem when i tried to send a mail from Access 2007.
I never found a direct function to insert the signature.

1) But by trying I found out that this unavailable internal function is executed by calling the first time
.Display
or
.Send
after creating the mail-object. But it is destroying the contents of the mailbody. Therefor you have to insert your part of the body after this command.

2) If you are using HTML-format, you should insert your mailbody after the "<body>"-tag in the HTML-Code. And the mailbody has to be a correct part of HTML-code. Otherwise you may get unpredictable results of the formatting of text.

The whole resulting Sub SendMail you can see in the attachment.
The only disadvantage of my solution is that if you want to send the mail immediately you will see the window of the mail for a short time until it will be closed by the .Send-Command.

greetings from germany
 

Attachments

  • Sendmail.txt
    1.5 KB · Views: 150
Last edited:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Outlook 2003 - Sending mails from Excel 2003 2
Need to Override Outlook 2007's security 1
Outlook Function help 3
VBA- from Access use WithEvents on Outlook 0
Sending message with multiple SMTP BCCs 2
Create email from Access on Outlook and make sure that email has b 1
Create email from Access on Outlook and make sure that email has beensent using WithEvents method 3
Setting the Reminder flag in VBA for e-Mails sent to users 5

Top