VBA-emailing

[Pobek]

Hi, I have VBA code that sends emails on outlook that works fine. However, the sent emails do not contain my logo and signature as set up in out look (even though composing & sending manually on outlook automatically compose and send on logo/signature-ready templates).

Can someone suggest what I need to tweak in the code below to enable my logo and signature to be attached to outgoing mails?

Code:
Sub SendRunRequest()

Dim Msg As String
Dim olApp As Object
Dim olEmail As Object
Dim SendAt As String
Dim SendTo As String
Dim Sendcc As String
Dim Subj As String

Dim resp As Long

Calculate





subje = Range("e24").Value
mes = Range("e26").Value
Tester = Range("e22").Value
ReqCC = Range("e23").Value

SendTo = Tester
Sendcc = ReqCC
Subj = subje
Msg = mes

'SendAt = "10/19/2008 12:30am" 'Date-Time must be in this format

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err = 429 Then
Err.Clear
Set olApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
olApp.Session.Logon

Set olEmail = olApp.CreateItem(olMailItem)
With olEmail
' .DeferredDeliveryTime = SendAt
.To = SendTo
.cc = Sendcc
.Subject = Subj
.Body = Msg

resp = MsgBox(Prompt:="Are you sure you want to send off this email?", _
Buttons:=vbYesNo, Title:="Warning")

If resp = vbYes Then
.Send
MsgBox "Email sent!"
End If

End With

olApp.Session.Logoff

Set olApp = Nothing
Set olEmail = Nothing


End Sub

 

[Max1616]

Try this:

Sub SendRunRequest()

Dim Msg As String
Dim olApp As Object
Dim olEmail As Object
Dim SendAt As String
Dim SendTo As String
Dim Sendcc As String
Dim Subj As String

Dim resp As Long

Calculate


subje = Range("e24").Value
mes = Range("e26").Value
Tester = Range("e22").Value
ReqCC = Range("e23").Value

SendTo = Tester
Sendcc = ReqCC
Subj = subje
Msg = mes

'SendAt = "10/19/2008 12:30am" 'Date-Time must be in this format

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err = 429 Then
Err.Clear
Set olApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
olApp.Session.Logon

Set olEmail = olApp.CreateItem(olMailItem)
olEmail.Display

With olEmail
' .DeferredDeliveryTime = SendAt
.To = SendTo
.cc = Sendcc
.Subject = Subj
.Body = Msg

resp = MsgBox(Prompt:="Are you sure you want to send off this email?", _
Buttons:=vbYesNo, Title:="Warning")

If resp = vbYes Then
.Send
MsgBox "Email sent!"
End If

End With

olApp.Session.Logoff

Set olApp = Nothing
Set olEmail = Nothing


End Sub

Need to add the olEmail.Display code, gives Outlook a chance to actually populate the signature.

Hope this helps!

 

[Pobek]

Try this:

Sub SendRunRequest()

Dim Msg As String
Dim olApp As Object
Dim olEmail As Object
Dim SendAt As String
Dim SendTo As String
Dim Sendcc As String
Dim Subj As String

Dim resp As Long

Calculate


subje = Range("e24").Value
mes = Range("e26").Value
Tester = Range("e22").Value
ReqCC = Range("e23").Value

SendTo = Tester
Sendcc = ReqCC
Subj = subje
Msg = mes

'SendAt = "10/19/2008 12:30am" 'Date-Time must be in this format

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err = 429 Then
Err.Clear
Set olApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
olApp.Session.Logon

Set olEmail = olApp.CreateItem(olMailItem)
olEmail.Display

With olEmail
' .DeferredDeliveryTime = SendAt
.To = SendTo
.cc = Sendcc
.Subject = Subj
.Body = Msg

resp = MsgBox(Prompt:="Are you sure you want to send off this email?", _
Buttons:=vbYesNo, Title:="Warning")

If resp = vbYes Then
.Send
MsgBox "Email sent!"
End If

End With

olApp.Session.Logoff

Set olApp = Nothing
Set olEmail = Nothing


End Sub

Need to add the olEmail.Display code, gives Outlook a chance to actually populate the signature.

Hope this helps!

Hi, Thanks for that, unfortunately it still did not work. All it did was to display outlook as it sent the mail...the signature and logo still excluded.

 

[Max1616]

If you run this, does the signature show in the displayed email?

Sub SendRunRequest()

Dim Msg As String
Dim olApp As Object
Dim olEmail As Object
Dim SendAt As String
Dim SendTo As String
Dim Sendcc As String
Dim Subj As String

Dim resp As Long

Calculate


subje = Range("e24").Value
mes = Range("e26").Value
Tester = Range("e22").Value
ReqCC = Range("e23").Value

SendTo = Tester
Sendcc = ReqCC
Subj = subje
Msg = mes

'SendAt = "10/19/2008 12:30am" 'Date-Time must be in this format

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err = 429 Then
Err.Clear
Set olApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
olApp.Session.Logon

Set olEmail = olApp.CreateItem(olMailItem)
olEmail.Display

End Sub

 

[Pobek]

If you run this, does the signature show in the displayed email?

Sub SendRunRequest()

Dim Msg As String
Dim olApp As Object
Dim olEmail As Object
Dim SendAt As String
Dim SendTo As String
Dim Sendcc As String
Dim Subj As String

Dim resp As Long

Calculate


subje = Range("e24").Value
mes = Range("e26").Value
Tester = Range("e22").Value
ReqCC = Range("e23").Value

SendTo = Tester
Sendcc = ReqCC
Subj = subje
Msg = mes

'SendAt = "10/19/2008 12:30am" 'Date-Time must be in this format

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err = 429 Then
Err.Clear
Set olApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
olApp.Session.Logon

Set olEmail = olApp.CreateItem(olMailItem)
olEmail.Display

End Sub

Hi,

No it does not. Neither the signature nor the loggo show in the sent email.

 

[Max1616]

Don't look at the sent email. The code I pasted above should only display an open email on your screen (It doesn't send). If that doesn't show the signature then the only other way I can think to grab the signature is to reference the Outlook file that stores the signature. I'm not sure where that would live though.

 

[Pobek]

Don't look at the sent email. The code I pasted above should only display an open email on your screen (It doesn't send). If that doesn't show the signature then the only other way I can think to grab the signature is to reference the Outlook file that stores the signature. I'm not sure where that would live though.

Hi Max,

I am not quite sure that an open email on the screen is the solution Im alluding to. The model sends email to clients, it is essential as a marketing plow to have my company logo display on the emails they get so that I keep displaying my brand. So is the signature. My thought is if its not in my sent box then neither will they get it in their inbox.

 

[Max1616]

Hi, the reason I ask if it was in the signature of the displayed email is because if it is then the answer to this problem is to first display the email to give Outlook time to populate the signature, then we can have it send the email after it displays it. I wasn't trying to solve the problem with the above code, I was trying to trouble shoot what's happening and then provide a solution. That all being said, does the signature populate using the display code I provided?

 

[Pobek]

Hi, the reason I ask if it was in the signature of the displayed email is because if it is then the answer to this problem is to first display the email to give Outlook time to populate the signature, then we can have it send the email after it displays it. I wasn't trying to solve the problem with the above code, I was trying to trouble shoot what's happening and then provide a solution. That all being said, does the signature populate using the display code I provided?

Hi Max,

No the signature and logo aren't displaying with the code. (Neither was it in the displayed email). Cheers