none
Redemption.SafeMailItem: Code for Excel 03/07 not working in 2010 RRS feed

  • Question

  • I am trying to have some code that will allow a workbook to be sent to a recipient at the press of a button. The below is what I currently have, and this works in Excel 03/07 with Outlook 03. However, there are some users who have Excel and Outlook 2010 and the code doesn't work for them. It doesn't have any errors, it simple does nothing. If I change '.Send to .Send it works for the 2010 users, but the 03 and 07 users get the security message, which I’d like to avoid.

    Any help would be greatly appreciated.

    Dim wb1 As Workbook

        Dim TempFilePath As String

        Dim TempFileName As String

        Dim FileExtStr As String

        Dim OutApp As Object

        Dim OutMail As Object

        With Application

            .ScreenUpdating = False

            .EnableEvents = False

        End With

        Set wb1 = ActiveWorkbook

        TempFilePath = Environ$("temp") & "\"

        TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

        FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

        wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

        Set OutApp = CreateObject("Outlook.Application")

        Set OutMail = OutApp.CreateItem(0)

        On Error Resume Next

        With OutMail

            .to = "Emal@addess.com"

            .CC = ""

            .BCC = ""

            .Subject = "Test"

            .Body = ""

            .Attachments.Add TempFilePath & TempFileName & FileExtStr

            'You can add other files also like this

            '.Attachments.Add ("C:\test.txt")

            '.Send   'or use .Display

           

            Set sItem = CreateObject("Redemption.SafeMailItem")

    sItem.Item = OutMail

    sItem.Send

           

           

        End With

        On Error GoTo 0

        'Delete the file

        Kill TempFilePath & TempFileName & FileExtStr

        Set OutMail = Nothing

        Set OutApp = Nothing

        With Application

            .ScreenUpdating = True

            .EnableEvents = True

        End With

       

        MsgBox "Your Request has been Submitted"

    End Sub

    Thursday, April 17, 2014 10:27 AM

Answers

All replies

  • Hello Ben,

    Redemption is a commercial component. I'd suggest contacting the Redemption developer at http://www.dimastr.com/redemption/contact.htm instead.

    When to do unsafe operations with Outlook object model, for example, call the Send method, you get an Outlook security prompt. You can read more about them in the Outlook "Object Model Guard" Security Issues for Developers article. There are different ways to avoid security prompts:

    1. Use Extended MAPI code instead. Redemption (as Outlook) is just a wrapper around Extended MAPI, that's why you don't get security warnings.

    2. Or the Outlook Security Manager component. You can read more about that tool in the article I mentioned earlier.

    3. Trusted COM add-ins. See Outlook 2002 COM add-ins are not trusted if they are created in Visual Studio .NET for more information.

    Thursday, April 17, 2014 11:14 AM
  • What you should do is set your SafeMailItem from the Outlook.MailItem and then set the various properties and call the various methods you want. Anything not directly exposed as part of the SafeMailItem object will be passed to the underlying Outlook.MailItem object. That should avoid all the security prompts.

    Ken Slovak MVP - Outlook

    Thursday, April 17, 2014 1:54 PM
    Moderator
  • Can you step through the code when it does not work? Is Outlook running at the time the code is executed? Does the message get sent when Outlook is opened?

    The code looks fine to me, but I'd add the following immediately after the "Set OutApp = CreateObject("Outlook.Application")" line:

    set olNS = OutApp.GetNamespace("MAPI")
    olNS.Logon


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Thursday, April 17, 2014 2:08 PM
  • Thanks Ken,

    How would I go about doing that? Sorry for the lack of knowledge on this subject.

    Monday, April 28, 2014 3:54 PM
  • Thanks Dmitry,

    I have added the line you suggested, but it has not made a difference. It still works in '07 but not 2010.

    The part ( '.Send   'or use .Display) has the (') at the front so the .Send doesn't affect the security in 07. I'm not sure if you noticed the ('), if i remove it, it works in 2010 but the security popup happens in '03 and '07.

    Sorry if I have misunderstood.

    Monday, April 28, 2014 3:59 PM
  • sItem.Item = OutMail

    Use that before setting properties on the item. Set the properties that exist on SafeMailItem using sItem, only set properties on OutMail where they don't exist on sItem.


    Ken Slovak MVP - Outlook

    Monday, April 28, 2014 4:34 PM
    Moderator