Hey guys I wrote some code that will let users send emails directly from Excel using Outlook. I would like to bypass the confirmation form. Is there a way to do that? Thx for the answers.
Skip comfirmation when sending email
-
-
-
Re: Skip comfirmation when sending email
What confirmation form...? Do you mean the pop-up saying something like "... an application is trying to send an email"?
-
Re: Skip comfirmation when sending email
Hi,
have a look here:
http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=604. -
Re: Skip comfirmation when sending email
Yes it's the one on the second picture on the link Chris posted. I'll have a look at the link. I'm sure it'll help me fix the problem.
P.S. I tried to upload the screenshot of the popup but it was like 10 times too big :D. -
Re: Skip comfirmation when sending email
I have used ClickYes in a project, it works well, here is some code I used to automate starting & stopping
Regards
RichardCode
Display MorePrivate Sub Turn_Auto_Yes_On() Dim wnd As Long Dim uClickYes As Long Dim Res As Long uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME") wnd = FindWindow("EXCLICKYES_WND", 0&) Res = SendMessage(wnd, uClickYes, 1, 0) End Sub Private Sub Turn_Off_Auto_Yes() Dim wnd As Long Dim uClickYes As Long Dim Res As Long uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME") wnd = FindWindow("EXCLICKYES_WND", 0&) Res = SendMessage(wnd, uClickYes, 0, 0) End Sub Public Sub StartClickYes() Dim strComputer As String, objWMI As Object, proc As Object strComputer = "." Set objWMI = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") Set proc = objWMI.ExecQuery("Select * from Win32_Process Where Name = 'ClickYes.exe'") If proc.Count = 0 Then If Location = 1 Then Call Shell("C:\Program Files\Express ClickYes\ClickYes.exe", 1) ElseIf Location = 2 Then Call Shell("C:\Program Files (x86)\Express ClickYes\ClickYes.exe", 1) ElseIf Location = 3 Then Call Shell(Range("ClickYes"), 1) End If End If End Sub Public Sub ClickYesExists(ByRef Continue As String) Dim Installed As Boolean Installed = False If Dir$("C:\Program Files\Express ClickYes\ClickYes.exe") <> vbNullString Then Installed = True Location = 1 ElseIf Dir$("C:\Program Files (x86)\Express ClickYes\ClickYes.exe") <> vbNullString Then Installed = True Location = 2 ElseIf Dir$(Range("ClickYes")) <> vbNullString Then Installed = True Location = 3 Else MsgBox "The ClickYes program does not appear to be installed at its normal location" & Chr$(13) & Chr$(13) & "You must install it to send email or specify where it is installed in the Email Settings on the Settings page." Continue = "No" End If End Sub Public Sub CloseClickYes() Dim strTerminateThis As String Dim objWMIcimv2 As Object Dim objProcess As Object Dim objList As Object Dim intError As Long strTerminateThis = "ClickYes.exe" Set objWMIcimv2 = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\.\root\cimv2") Set objList = objWMIcimv2.ExecQuery("select * from win32_process where name='" & strTerminateThis & "'") If objList.Count = 0 Then Set objWMIcimv2 = Nothing Set objList = Nothing Set objProcess = Nothing Exit Sub Else For Each objProcess In objList intError = objProcess.Terminate If intError <> 0 Then Exit Sub End If Next Set objWMIcimv2 = Nothing Set objList = Nothing Set objProcess = Nothing Exit Sub End If End Sub
-
-
Re: Skip comfirmation when sending email
I have had no luck trying to get any of the solutions above to work (still got to test yours Richard). But your solition seems to require me to install the program, which is something I would like to avoid, because there are multiple users for my excel workbook, and I'd rather have them click yes every time than having to install something on every of their computers. If I can solve this in my workbook I will, if not, then they will have to deal with it. So far I havent had any complaints about it, but I'm still trying to optimize everything :D.
-
Re: Skip comfirmation when sending email
Prior to using ClickYes I tried this, from memory it bypasses Outlook so you don't get a record of sent files and I had issues getting it to work on a business network that would have been using Exchange, it worked great at home through a pop3 account
Code
Display MorePrivate Declare Function RegisterWindowMessage Lib "user32" Alias "RegisterWindowMessageA" (ByVal lpString As String) As Long Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName As Any) As Long Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Sub EmailResults() Dim iMsg As Object, iConf As Object, RecipientName As String Dim Flds As Variant With Application .ScreenUpdating = False .EnableEvents = False End With Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 ' CDO Source Defaults Set Flds = iConf.Fields With Flds .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2 .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "mail.yourISP.com" .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25 .Update End With With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & "\" .Title = "Select the Folder with the PDF's to email." .Show If .SelectedItems.Count = 0 Then Exit Sub Else FilePath = .SelectedItems(1) End If End With Set iMsg = CreateObject("CDO.Message") With iMsg Set .Configuration = iConf .To = "Something" .From = "Someone" .Subject = "Something" .TextBody = "Something" 'Attach a file FileName = A Variable Name .AddAttachment FilePath & "\" & FileName .Send End With Set iMsg = Nothing With Application .EnableEvents = True .ScreenUpdating = True End With End If ErrorHandler: 'MsgBox Err.Number & Err.Description Select Case Err.Number Case -2147024894 MsgBox "File " & FileName & " is not in this folder." & Chr(13) & Chr(13) _ & "Try again" Case -2147220978 MsgBox "You must provide a ""From"" name in cell C2 and an Email address in cell D2 in the " & EmailPageName & " Worksheet." End Select End Sub
-
Re: Skip comfirmation when sending email
Richard I did try to use your code, but since I don't want to attach PDFs I had to rework it a little. Not sure why, but it stops at .Sent. Could you have a look at it please?
Code
Display MoreSub EmailResults() Dim iMsg As Object, iConf As Object, RecipientName As String Dim Flds As Variant With Application .ScreenUpdating = False .EnableEvents = False End With Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 ' CDO Source Defaults Set Flds = iConf.Fields With Flds .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2 '// just picked one smtp server from a list... .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "smpt.telkom.net" .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25 .Update End With Set iMsg = CreateObject("CDO.Message") With iMsg Set .Configuration = iConf .To = "[email protected]" .From = "Someone" .Subject = "Something" .TextBody = "Something" .Send End With Set iMsg = Nothing With Application .EnableEvents = True .ScreenUpdating = True End With End Sub
-
Re: Skip comfirmation when sending email
Been a while since I used the code, found this page http://www.paulsadowski.com/wsh/cdo.htm Paul has lots of examples, maybe its the "smpt.telkom.net" I take it thats your ISP and &amp;amp;quot;[email protected]&amp;amp;quot;[/email] should be your own email address (not that you would display here for all to see!)
-
Re: Skip comfirmation when sending email
Found this code at http://gallery.technet.microsoft.com/scriptcenter/1…48-cb108ce6e523 supplied by Ron Nixon that worked for me when I used my ISP and email address. I'm using win7 64bit
Code
Display MoreSub email() Set objEmail = CreateObject("CDO.Message") Set objConf = CreateObject("CDO.Configuration") Set objFlds = objConf.Fields With objFlds .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2 .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "smpt.telkom.net" .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25 '.Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate[/URL]") = cdoBasic .Update End With Set objEmail.Configuration = objConf objEmail.From = "[EMAIL="[email protected]"][email protected][/EMAIL]" objEmail.To = "[EMAIL="[email protected]"][email protected][/EMAIL]" objEmail.Subject = "All done!" objEmail.Textbody = "...here you go..." 'objEmail.Addattachment "C:\directory name of file\filename" objEmail.Send End Sub
-
-
Re: Skip comfirmation when sending email
Yes that [email protected] is a fake name I put here just for show. I tried your last posted solution and I get a syntax error (the rows posted below are colored red)
CodeobjEmail.From = "[EMAIL="[email protected]"][email protected][/EMAIL]" objEmail.To = "[EMAIL="[email protected]"][email protected][/EMAIL]"
Any idea why?
p.s. I probably got the smtp wrong or something, still trying to figure it out. -
Re: Skip comfirmation when sending email
Something funny about the code I pasted, looks like it doubled up in those lines for some unknown reason, have a look at the last link I posted. Yep everytime I try it wraps it with email quotes,
I think you have a spelling error, it should be smtp not smpt "Simple Mail Transfer Protocol" -
Re: Skip comfirmation when sending email
I still get the syntax error. The rows are still in red. And it is not a typo, unless it's a typo on the site I looked up that smtp. But I also tried many others and none worked. No idea what am I doing wrong.
-
Re: Skip comfirmation when sending email
Is your email a pop3 account or is it Exchange? Thats where I came unstuck when trying to implement an Excel application for a university and the IT fella's aren't always keen to help some non IT with his latest Excel app. Maybe Google some more about CDO and Exchange if thats the issue, otherwise its a bit hard to help when we can't replicate your circumstances.
Regards
Richardps If its not compiling then thats where you need to start
-
Re: Skip comfirmation when sending email
It's exchange. It often happens to me that code I find on the internet will not compile. Any idea why? Should I be looking for libraries or could the cause be something else?
-
-
Re: Skip comfirmation when sending email
I used a free add-in from mapilab.com named 'Advanced security for outlook' to skip this confirmation.
-
Re: Skip comfirmation when sending email
Thx for all the help everyone! I have installed Advanced security for outlook and it works perfectly. This will be a temporary solution till I figure some other way.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!