Email from Excel - 2 Part Question
 

Hello,

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

The first part is about the message box the below brings up when its
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 its
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 its sent from. As you can see right
now, Ive 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€� - Id 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 ")

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

.Send
End With
End Sub