Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reposting for help with Outlook from Excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reposting for help with Outlook from Excel
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 On Jan 14, 11:16 am, Jenny B. wrote: 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 ") Const olOriginator = 0 If Len(aFrom) 0 Then .Recipients.Add(aFrom).Type = olOriginator .Send End With End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reposting for help with Outlook from Excel
Thank you. I'll try both and ideas and look at the enclosed sites for
further info. Have a great day - Jenny B. "JP" wrote: 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 On Jan 14, 11:16 am, Jenny B. wrote: 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 ") Const olOriginator = 0 If Len(aFrom) 0 Then .Recipients.Add(aFrom).Type = olOriginator .Send End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reposting lost query about virus scan | Excel Discussion (Misc queries) | |||
Sorry for reposting - but this is driving me crazy! | Excel Discussion (Misc queries) | |||
Sum of comma seperated values in cell(Reposting) | Excel Worksheet Functions | |||
Vlookup Formula Limit Error ? (Reposting) | Excel Worksheet Functions |