locked
Excel to Outlook - Attach active book / Include default mail signature RRS feed

  • Question

  • I have a macro that opens and formats a mail message (in Outllook) based on information in an Excel workbook. I would like some help with two items...

     

    1. How do I call the default signature for Outlook new mail items so that it appears in this new message? (I can't get my default signature to load like when I click new mail message in Outlook)

     

    2. Is there a way to include the workbook in this new mail message (as an attachment - not for review).

     

    To give you an idea of what I'm doing the example is below. (I've changed the company names and email addresses to protect the innocent Smile )

     

     

     

    This code runs from the active workbook...

     

     

    Code Snippet

    Option Explicit
    Sub SendEmail()

    Dim boxresult As Single

    If ActiveWorkbook.Name = "Cylinder Work Scope" Then
        boxresult = MsgBox("Save with a different FileName", 48, "Warning")
        If boxresult = 0 Then
        Exit Sub
        Else
        End If
    End If

     

        Dim OutlookApp As Object
        Dim MItem As Object
        Dim Subj As String
        Dim EmailAddr As String
        Dim Recipient As String
        Dim CHLJob As String
        Dim NPN As String
        Dim Req As Double
        Dim Msg As String
       
        Set OutlookApp = CreateObject("Outlook.Application")
       
      NPN = Range("D5").Value
      Req = Range("Q5").Value
      CHLJob = Range("Z5").Value
      Subj = "Job " & CHLJob & "  NPN " & NPN & "  Req# " & Req
     
     
    'Email Addresses & recipients
        If Range("Q6").Value = "Company A" Then
            EmailAddr = "a@company.com"
            Recipient = "Thomas"
            ElseIf Range("Q6").Value = "Company B" Then
            EmailAddr = "b@company.com"
            Recipient = "Kenny"
                ElseIf Range("Q6").Value = "CompanyC" Then
                EmailAddr = "c@company.com"
                Recipient = "Ellen"
                    ElseIf Range("Q6").Value = "CompanyD" Then
                    EmailAddr = "d@company.com"
                    Recipient = "Bob"
                        ElseIf Range("Q6").Value = "CompanyE" Then
                        EmailAddr = "e@lastcompany.com"
                        Recipient = "Nick"
                            Else: EmailAddr = ""
                            Recipient = ""
                        End If
     
     
     
    'Message Body
      If Range("AE5").Value = 0 Then
        Msg = Recipient & "," & vbCrLf & vbCrLf
        Msg = Msg & "Attached is our Scope of Work for the above new job. "
        Msg = Msg & "Please reference the latest drawing revisions being sent by document control."
        Msg = Msg & vbCrLf & "Feel free to contact me should you have any questions."
      Else
        Msg = Recipient & "," & vbCrLf & vbCrLf
        Msg = Msg & "Attached is our revised Scope of Work for the above job. "
        Msg = Msg & "Please reference the latest drawing revisions being sent by document control."
        Msg = Msg & vbCrLf & "Feel free to contact me should you have any questions."
     End If
     
     'Create Mail Item
     Set MItem = OutlookApp.CreateItem(0)
     With MItem
        .To = EmailAddr
        .Subject = Subj
        .Body = Msg
        .Display
    End With
     
     
    End Sub

     

     

    Any help with this would be greatly appreciated!
    Monday, March 3, 2008 4:01 PM

Answers

  • I figured out how to attach the open workbook in the mail message. I added the following...

     

    Code Snippet
    .Attachments.Add ActiveWorkbook.FullName

     

     

     

    I still have not figured out how to add my signature in this message however. I don't see anything in the object model for an email signature either.

    By default Outlook includes my signature in all outgoing mail, but it is not adding it to the email created using the above code.

    Can somebody help me out please?

     

    Tuesday, March 4, 2008 6:26 PM
  • When you tried the html tags did you change your code to use .htmlbody=msg instead of .body=msg?

     

    You should be able to get around the quotes using Chr(34):

    msg = "<FONT face=" & Chr(34) & "Arial Black" & Chr(34) & " color=#0000ff size=2>text</FONT>"

     

    And as for the text editor for html - I've only ever written html in a text editor. The few times I've experimented with html editors I've disliked the amount of redundant rubbish they add. I use TextPad, which does at least have decent syntax highlighting (unlike WordPad). Mind you, I don't design web sites, I only use html for dialogue boxes and because one of the apps I've been using nests it's macro/scripting language inside html.

     

    Tuesday, March 11, 2008 10:36 PM

All replies

  • I figured out how to attach the open workbook in the mail message. I added the following...

     

    Code Snippet
    .Attachments.Add ActiveWorkbook.FullName

     

     

     

    I still have not figured out how to add my signature in this message however. I don't see anything in the object model for an email signature either.

    By default Outlook includes my signature in all outgoing mail, but it is not adding it to the email created using the above code.

    Can somebody help me out please?

     

    Tuesday, March 4, 2008 6:26 PM
  • Hi

     

    I have never found out how to add a signature as you describe. I have always added the text to the end of the mail body i.e. add some carraige returns and the text to your Msg variable. If you find how to add a standard signature please post this as it would be a great help.

     

    Wednesday, March 5, 2008 11:39 AM
  • So far everything I have read says that it can't be done, and that the work-around is as you said, to enter it in the message body.

    That brings me to another question then...

     

    How to format the text for my 'signature'.

    For instance, my name is Arial Black, Color Blue, Font Size 10

    My title, address, phone, etc. is Arial, 10, Dark Red

    My Email address however is Times new Roman, 12, Blue, italicized and underlined.

     

    All of my effort have proven fruitless. It appears that unless it is an object you can't format the text?

    Can somebody help?

    Wednesday, March 5, 2008 3:02 PM
  • Hi

     

    If you change the body type to HTML then you can format the text, I am not expert on HTML so cannot advise the tags etc you need, but I guess there are plenty of references for what you will need

    Thursday, March 6, 2008 8:49 AM
  • My default mail format is set to HTML. But is it really possible to format using Hyper Text from within VBA?
    I have not found anything as of yet that explains how to do this.
    Monday, March 10, 2008 5:31 PM
  • Html tags are still text, they're just interpreted differently by the html viewer.

     

    For example, bold is tagged like this:

    Code Snippet

    <STRONG>This is bold</STRONG>

     

    To see the html for your regular signature, go into Outlook options and call up the signature editor with your signature in it. Right click on the background of the edit pane and choose "View Source". That should call up Notepad (or whatever your default text editor is) with the html code for your signature. If your VBA assigns all of that to the string, the html viewer should display it the same as it displays your regular signature.

     

    Note I haven't actually tried any of that... but html code is just text so there should be no reason you can't assign it via VBA.

    Monday, March 10, 2008 9:39 PM
  • HTML - ahh yes. 15 years ago I was writing web sites using Wordpad and PERL - yes I typed everything.
    (We didn't need no stinkin' editor! Is my age showing now?)

    Anyway - I gave it several tries. The VBE doesn't like the tags very much; quotes and the greater/less than thingies confuse it it gives compile errors and syntax errors all over the place.
    But even with just one simple tag, when the email is generated, the tag is treated as text and is visible. For grins I wrote a simple web page and viewed it in IE to make sure it worked. Then I copied and pasted the code into a new Outlook message, it treated all tags as text.

    For instance...

    Code Snippet

    <FONT face="Arial Black" color=#0000ff size=2>text</FONT>


    The VBE doesn't like the quotes around Arial Black, but it also doesn't like it without the quotes. It also does not like the entire line unless there are quotes around everything (it looks for an expression).

    So unless there is a trick, or way to tell the VBE editor that there are hyper text tags, I don't see a solution to my problem.
    But i know there has to be someone who can figure out a work-around - isn't there?

    Tuesday, March 11, 2008 3:22 PM
  • When you tried the html tags did you change your code to use .htmlbody=msg instead of .body=msg?

     

    You should be able to get around the quotes using Chr(34):

    msg = "<FONT face=" & Chr(34) & "Arial Black" & Chr(34) & " color=#0000ff size=2>text</FONT>"

     

    And as for the text editor for html - I've only ever written html in a text editor. The few times I've experimented with html editors I've disliked the amount of redundant rubbish they add. I use TextPad, which does at least have decent syntax highlighting (unlike WordPad). Mind you, I don't design web sites, I only use html for dialogue boxes and because one of the apps I've been using nests it's macro/scripting language inside html.

     

    Tuesday, March 11, 2008 10:36 PM
  • Chr(34) did the trick, the VBE now accepts the quotes.

    I did not use .htmlbody the first time (was unaware of it's existence). This time around it worked beautifully!

     

     

    One note...

     When using .htmlbody some constants no longer work (maybe all?), vbCr and vbCrLf are not recognized so all formatting must be done in html when going this route.

     
    Wednesday, March 12, 2008 2:28 PM