Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Location
    Cypress, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Security Msg Hiding Offscreen (2002 sp3)

    I am using the SendMail feature in a macro and it works fine except for some odd reason it sometimes places the Security message dialog-window off the visible screen and the user doesnt see it to reply to so it looks to them like Excel has hung up. Any way to make sure this dialog shows on screen ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Security Msg Hiding Offscreen (2002 sp3)

    Do you mean that the dialog is hidden behind other windows, or that is really offscreen (how would you check that?)

    Users can download and install the free utility ClickYes. It'll suppress the security warning. See post 488,173 for other options to avoid the security warning.

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Location
    Cypress, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Security Msg Hiding Offscreen (2002 sp3)

    It is offscreen (way off to the left). I found this by left clicking the "Microsoft Outlook" spreadsheet item on the task bar and selecting Move, then used the cursor keys to slowly drag it back on to the screen.
    Installing the ClickYes utility or anything else is not allowed by (very strict) corp policies. Is there any VBA method for finding & repositioning this dialog window ?

    NOTE: user desktops vary -- some have dual monitors controlled by nVidea's nView - some only have single monitor.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Security Msg Hiding Offscreen (2002 sp3)

    Sorry, no idea.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Security Msg Hiding Offscreen (2002 sp3)

    If you're sending just the odd message, then it might help to just show the prepared message instead of trying to send it. If the users have to click a button anyway, it might just as well be the send button on the email message. AFAIK It will avoid the security warning.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    New Lounger
    Join Date
    Dec 2004
    Location
    Cypress, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Security Msg Hiding Offscreen (2002 sp3)

    its the tail end of a macro that formats a report worksheet and then sends that one sheet as an attachment. Code is as follows:

    ActiveWorkbook.Save
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:=KeepFile, AddToMru:=True
    ActiveWorkbook.SendMail _
    Recipients:="TheTeamMailbox@ZZZ.com", _
    Subject:="Morning Report - " & RPTname

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Security Msg Hiding Offscreen (2002 sp3)

    You might check out Ron de Bruin's site on mailing from Excel:

    http://www.rondebruin.nl

    If I were you I would use Outlook automation to create the message, like this:

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Sub</font color=blue> CreateMail(sMailAddress <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>, sSubj <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>, sMsg <font color=blue>As</font color=blue> String, sFilename <font color=blue>As</font color=blue> String)
    <font color=blue>Dim</font color=blue> oMailItem <font color=blue>As</font color=blue> <font color=blue>Object</font color=blue>
    <font color=blue>Dim</font color=blue> oOLapp As <font color=blue>Object</font color=blue>
    <font color=blue>Set</font color=blue> oOLapp = CreateObject("Outlook.application")
    <font color=blue>Set</font color=blue> oMailItem = oOLapp.CreateItem(0)
    <font color=blue>With</font color=blue> oMailItem
    .to = sMailAddress
    .Subject = s<font color=blue>Sub</font color=blue>j
    .Body = sMsg
    .attachments.Add sFilename
    .Display
    <font color=blue>Set</font color=blue> oOLapp = <font color=blue>Nothing</font color=blue>
    <font color=blue>Set</font color=blue> oMailItem = <font color=blue>Nothing</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>With</font color=blue>
    bSentOne = <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> Sub

    </font color=black></code></div hiblock>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    New Lounger
    Join Date
    Dec 2004
    Location
    Cypress, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EUREKA - A SOLUTION!

    I found the solution! It works slightly differently but it works!

    Instead of using Application.SendMail I am using Application.Dialogs(xlDialogSendMail).Show as follows:

    <pre> ActiveWorkbook.Save
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:=KeepFile, AddToMru:=True
    email_Recipients = "TheTeamMailbox@ZZZ.com"
    email_Subject = "Morning Report - " & RPTname
    Application.Dialogs(xlDialogSendMail).Show email_Recipients, email_Subject
    </pre>



    Credit: This tip was found at http://www.vbaexpress.com/forum/showthread.php?t=3008 <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: EUREKA - A SOLUTION!

    Thanks for sharing the solution!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •