excel - How to display signature in macro used for sending mail from outlook? -

currently have created macro have content body , after want show default signature of outlook user .

i have 2 buttons in excel , 2 macro 1 each.

.display think responsible signature display when using , signature not coming first macro , in second fuction coming on

the top of body also. please advise do. below macros:-

    sub email()              dim olapp object     set olapp = createobject("outlook.application")     dim mynamesp object      'set mynamesp = createobject("outlook.namespace")     dim myinbox object      'set myinbox = createobject("outlook.mapifolder")     dim myexplorer object      'set myexplorer = createobject("outlook.explore")     dim newmail object      'set newmail = createobject("outlook.mailitem")     dim outopen boolean     dim namelist  string     dim lastrow integer     dim cclist string     dim result integer     dim resultto integer     dim resultcc integer     dim cclistappned string       'count last working row     activesheet     lastrow = .cells(.rows.count, "a").end(xlup).row     end    dim integer       = 11 lastrow 'use cells 7 39 in column "i" names stored      if sheets("sheet1").range("a" & i).value = "y"       if sheets("sheet1").range("h" & i).value <> ""       resultto = instr(namelist, sheets("sheet1").range("h" & i).value)        if (resultto = 0)         namelist = namelist & ";" & sheets("sheet1").range("h" & i).value         end if        result = instr(cclist, sheets("sheet1").range("l" & i).value)        if (result = 0)         cclist = cclist & ";" & sheets("sheet1").range("l" & i).value         end if          resultcc = instr(cclistappned, sheets("sheet1").range("n" & i).value)        if (resultcc = 0)         cclistappned = cclistappned & ";" & sheets("sheet1").range("n" & i).value         end if          end if     end if        next       cclist = cclist & cclistappned         ' check see if there's explorer window open         ' if not open new 1         outopen = true         set myexplorer = olapp.activeexplorer         if typename(myexplorer) = "nothing"             outopen = false             set mynamesp = olapp.getnamespace("mapi")             'set myinbox = mynamesp.getdefaultfolder(olfolderinbox)            ' set myexplorer = myinbox.getexplorer         end if          ' if  don't display outlook while sending email comment below statement         'otherwise can un-comment          'myexplorer.display          ' create new mail message item.         set newmail = olapp.createitem(0)         newmail             '.display ' don't have show e-mail send              .display             .subject = "audit response requested - ["             .subject = .subject & sheets("sheet1").range("e2").value & "/"             .subject = .subject & sheets("sheet1").range("e1").value & "]"              .to = namelist             .cc = cclist              .htmlbody = "<b><h2 style=color:blue; background-color:yellow><p style=background: yellow><center>please use voting buttons above facilitate reply. </center></p></h2></b>"             .htmlbody = .htmlbody & "we have been asked <b>" & sheets("sheet1").range("e2").value & "</b>, furnish information in conjunction annual financial audit. "             .htmlbody = .htmlbody & "according firm's records, have recorded time on matters company [<b> [and/or subsidiaries]</b> since last annual audit. "             .htmlbody = .htmlbody & "[<b> our last letter (and exhibit a) printed out below. </b>] accordingly, please respond "             .htmlbody = .htmlbody & "whether or not have material report. " & "<b>[please send [email sender] if have questions materiality thresholds.] [our response due [date]].</b>" & "  thank you!"             .htmlbody = .htmlbody & "<br><br>" & "for information:" & "<br><br>"             .htmlbody = .htmlbody & "1.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; aware of (1) pending litigation, or (2) overtly threatened litigation, meaning potential claimant has manifested company awareness of , present intention assert possible claim or assessment?"             .htmlbody = .htmlbody & "<br>" & "2.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; aware of or have worked on matter company may involve unasserted possible claim or assessment may call financial statement disclosure? financial statement disclosure of material unasserted claims or assessments may required in following cases:"             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp(a)&nbsp;&nbsp;&nbsp;where there has been manifestation potential claimant of awareness of possible claim or assessment , there reasonable possibility outcome unfavorable, or  "             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp(b)&nbsp;&nbsp;&nbsp;where there has been no manifestation potential claimant of awareness of possible claim or assessment considered probable claim asserted , there reasonable possibility outcome unfavorable.  examples of include following: "             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;(i) catastrophe, accident, or other similar physical occurrence in client's involvement open , notorious, or"             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp(ii) investigation government agency enforcement proceedings have been instituted or likelihood not instituted remote, under circumstances assertion of 1 or more private claims redress expected, or"             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp(iii) public disclosure client acknowledging (and focusing attention upon) existence of 1 or more probable claims arising out of event or circumstance"             .htmlbody = .htmlbody & "<br>" & "3.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; have during period in question called client's attention matters thought client should consider financial statement disclosure? <b/>"             .htmlbody = .htmlbody & "<br>" & "<b>[4.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; aware of material litigation, claims or assessments relating company have been settled?]"             .htmlbody = .htmlbody & "<br>" & "<b>=============================<b/>" & "<br>" & "<b>last [annual] audit letter dated [***]<b/>"          .votingoptions = "nothing report;yes - please choose edit explain in email reply;"          end          'newmail.send         'if not outopen olapp.quit          'release memory.         set olapp = nothing         set mynamesp = nothing         set myinbox = nothing         set myexplorer = nothing         set newmail = nothing   end sub   sub reminder()     dim olapp object     set olapp = createobject("outlook.application")     dim mynamesp object      'set mynamesp = createobject("outlook.namespace")     dim myinbox object      'set myinbox = createobject("outlook.mapifolder")     dim myexplorer object      'set myexplorer = createobject("outlook.explore")     dim newmail object      'set newmail = createobject("outlook.mailitem")     dim outopen boolean     dim namelist  string     dim lastrow integer     dim cclist string     dim result integer     dim resultto integer     dim resultcc integer     dim cclistappned string        'count last working row     activesheet     lastrow = .cells(.rows.count, "a").end(xlup).row     end    dim integer      = 11 lastrow 'use cells 7 39 in column "i" names stored      if sheets("sheet1").range("b" & i).value = "" , sheets("sheet1").range("a" & i).value = "y"       if sheets("sheet1").range("h" & i).value <> ""       resultto = instr(namelist, sheets("sheet1").range("h" & i).value)        if (resultto = 0)         namelist = namelist & ";" & sheets("sheet1").range("h" & i).value         end if        result = instr(cclist, sheets("sheet1").range("l" & i).value)        if (result = 0)         cclist = cclist & ";" & sheets("sheet1").range("l" & i).value         end if          resultcc = instr(cclistappned, sheets("sheet1").range("n" & i).value)        if (resultcc = 0)         cclistappned = cclistappned & ";" & sheets("sheet1").range("n" & i).value         end if          end if     end if        next       cclist = cclist & cclistappned         ' check see if there's explorer window open         ' if not open new 1         outopen = true         set myexplorer = olapp.activeexplorer         if typename(myexplorer) = "nothing"             outopen = false             set mynamesp = olapp.getnamespace("mapi")            ' set myinbox = mynamesp.getdefaultfolder(olfolderinbox)             'set myexplorer = myinbox.getexplorer         end if          ' if  don't display outlook while sending email comment below statement         'otherwise can un-comment          'myexplorer.display          ' create new mail message item.         set newmail = olapp.createitem(0)         newmail             '.display ' don't have show e-mail send              .subject = "audit response requested - ["             .subject = .subject & sheets("sheet1").range("e2").value & "/"             .subject = .subject & sheets("sheet1").range("e1").value & "]"              .to = namelist             .cc = cclist             .htmlbody = .htmlbody & "this quick reminder our response " & sheets("sheet1").range("e2").value & " due. please respond below able. thanks!"             .htmlbody = .htmlbody & "<b><h2 style=color:blue background: #ffff00><p style=background: yellow><center>please use voting buttons above facilitate reply. </center></p></h2></b>"             .htmlbody = .htmlbody & "we have been asked <b>" & sheets("sheet1").range("e2").value & "</b>, furnish information in conjunction annual financial audit. "             .htmlbody = .htmlbody & "according firm's records, have recorded time on matters company [<b> [and/or subsidiaries]</b> since last annual audit. "             .htmlbody = .htmlbody & "[<b> our last letter (and exhibit a) printed out below. </b>] accordingly, please respond "             .htmlbody = .htmlbody & "whether or not have material report. " & "<b>[please send [email sender] if have questions materiality thresholds.] [our response due [date]].</b>" & "thank you!"             .htmlbody = .htmlbody & "<br><br>" & "for information:" & "<br><br>"             .htmlbody = .htmlbody & "1.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; aware of (1) pending litigation, or (2) overtly threatened litigation, meaning potential claimant has manifested company awareness of , present intention assert possible claim or assessment?"             .htmlbody = .htmlbody & "<br>" & "2.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; aware of or have worked on matter company may involve unasserted possible claim or assessment may call financial statement disclosure? financial statement disclosure of material unasserted claims or assessments may required in following cases:"             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp(a)&nbsp;&nbsp;&nbsp;where there has been manifestation potential claimant of awareness of possible claim or assessment , there reasonable possibility outcome unfavorable, or  "             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp(b)&nbsp;&nbsp;&nbsp;where there has been no manifestation potential claimant of awareness of possible claim or assessment considered probable claim asserted , there reasonable possibility outcome unfavorable.  examples of include following: "             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;(i) catastrophe, accident, or other similar physical occurrence in client's involvement open , notorious, or"             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp(ii) investigation government agency enforcement proceedings have been instituted or likelihood not instituted remote, under circumstances assertion of 1 or more private claims redress expected, or"             .htmlbody = .htmlbody & "<br>" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp(iii) public disclosure client acknowledging (and focusing attention upon) existence of 1 or more probable claims arising out of event or circumstance"             .htmlbody = .htmlbody & "<br>" & "3.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; have during period in question called client's attention matters thought client should consider financial statement disclosure? <b/>"             .htmlbody = .htmlbody & "<br>" & "<b>[4.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; aware of material litigation, claims or assessments relating company have been settled?]"             .htmlbody = .htmlbody & "<br>" & "<b>=============================<b/>" & "<br>" & "<b>last [annual] audit letter dated [***]<b/>"              .display         .votingoptions = "nothing report;yes - please choose edit explain in email reply;"          end          'newmail.send         'if not outopen olapp.quit          'release memory.         set olapp = nothing         set mynamesp = nothing         set myinbox = nothing         set myexplorer = nothing         set newmail = nothing   end sub 

the outlook object model doesn't provide anythyng signatures. can edit message body @ runtime using vba macros.

the outlook object model provides 3 main ways working item bodies:

1. body - string representing clear-text body of outlook item.
2. htmlbody - string representing html body of specified item.
3. word editor - microsoft word document object model of message being displayed. wordeditor property of inspector class returns instance of document class word object model can use set message body.

you can read more these ways in chapter 17: working item bodies. way choose customize signature in message body.

be aware, when work htmlbody property need add signature content (a formed html markup) before closing </body> tag, not append htmlbody string. so, find closing body tag in htmlbody string , insert signature there.