Reposting for help with Outlook from Excel

[Jenny B.]

Hello,

I have a two part question about a routine I’m using that sends an email
from Excel though Outlook.

The first part is about the message box the below brings up when it’s
running. The message brings up a prompt for the user to select “Yes†if they
want to proceed with their Outlook being opened. I can accept that, but it’s
annoying since the user already knows the requester opening their email is
me. I set up the Macro and created a Digital Certificate which they install
in the first place to even accept my macros, so this is more of an annoyance
than a help. Is there a way to suppress this from requesting them to accept
the process? Otherwise, they have to select†Yes†multiple times to finally
have it send.

The second part is based on the Sheet it’s sent from. As you can see right
now, I’ve created the subject line based on a Cell Range in the worksheet.
Is there also a way to create a Second Email based on a condition in that
same Sheet? This first email below needs to go out every time which it does
just fine. However, I have a cell in the Sheet (“F34â€) which when it states
“Yes†- I’d like to then have it create an 2nd email for a different group
based on that condition (something like a kind of Target Value ).

Thank you in advance – Jenny B.


Sub LisaEmail()

Dim Outlook
Set Outlook = CreateObject("Outlook.Application")

Dim Message
Set Message = Outlook.CreateItem(olMailItem)
With Message

.Subject = "New Issue" & " - " & ActiveSheet.Range("h5").Value
.body = "There is a new B55 Checklist for review." & vbNewLine &
vbNewLine & "After you've had a chance to sign off on the Checklist, please
forward to the DMS Group for final review." & vbNewLine & vbNewLine & "Thank
you - Jenny B."
Application.DisplayAlerts = False
.Recipients.Add ("[email protected]")

Const olOriginator = 0
If Len(aFrom) > 0 Then .Recipients.Add(aFrom).Type = olOriginator

.Send
End With
End Sub

 

[JP]

Jenny,

1. You probably have the OL security update, sorry but you are out of
luck unless you want to use CDO or Redemption to send your emails.
Check out:
http://www.rondebruin.nl/cdo.htm
http://www.dimastr.com/redemption/home.htm

2. You could use event code for this. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Range = ActiveSheet.Range("F34") Then
If Target.Value = "Yes" Then

' your code to send mail here

End If
End If

Application.EnableEvents = True
End Sub



HTH,
JP

 

[Jenny B.]

Thank you. I'll try both and ideas and look at the enclosed sites for
further info.

Have a great day - Jenny B.