?
Solved

Open new mail message via Access VBA problem

Posted on 2011-09-21
6
Medium Priority
?
881 Views
Last Modified: 2012-05-12
Hi Experts,

In an Access 2007 application, I use an onclick event to open a new mail message using the fonction attached.
It works with : SetForegroundWindow (objOutlookMsg) or objApp.Explorers.Item(2).Activate but not always.
Very rarely on my computer and often on my customer computer, the new mail message stays background (flashing orange on task bar)

How can I be sure that the new message is Foreground after the onclick subroutine is finished ?

Thanks for your help.

Option Compare Database
Declare Function apiFindWindow Lib "user32" Alias "FindWindowA" (ByVal strClassName As String, ByVal lpWindowName As Any) As Long
Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long

Public Sub SendOutlookMessage(strEmailAddress As String, strEmailCCAddress As String, strEmailBccAddress As String, strSubject As String, strMessage As String, blnDisplayMessage As Boolean, Optional strAttachmentFullPath As String)
    
'* Copy this code and paste it into a new Access
'* Module. Click Tools > References and make sure
'* that "Microsoft Office Outlook x.0 Object Library"
'* is checked.
'*
'* SendOutlookMessage "john@doe.com", "ccJane@doe.com", "bccSue@doe.com", "Subject", "Body of Message", False, "C:\My Documents\MyAttachmentFile.txt"
    
Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim strProcName As String
Const conPATH_TO_OUTLOOK As String = "OUTLOOK.EXE"

On Error Resume Next
strProcName = "SendOutlookMessage"

If apiFindWindow(CStr("rctrl_renwnd32"), 0&) = 0 Then
    RetVal = Shell(conPATH_TO_OUTLOOK, vbMaximizedFocus)
    Do While apiFindWindow(CStr("rctrl_renwnd32"), 0&) = 0
    Loop
End If

Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
    Set objApp = CreateObject("Outlook.Application")
End If

Set objOutlookMsg = objApp.CreateItem(olMailItem)
    
With objOutlookMsg
    .To = strEmailAddress
    .CC = strEmailCCAddress
    .BCC = strEmailBccAddress
    .subject = strSubject
    .Body = strMessage

    If Not IsMissing(strAttachmentFullPath) Then
        If Trim(strAttachmentFullPath) = "" Then
        Else
            Set objOutlookAttach = .Attachments.Add(strAttachmentFullPath)
        End If
    End If

    If blnDisplayMessage Then
        .Display
    Else
        .Send
    End If
End With
    
    'SetForegroundWindow (objOutlookMsg)
    objApp.Explorers.Item(2).Activate
    
    Set objApp = Nothing
    Set objOutlookMsg = Nothing
    Set objOutlookAttach = Nothing
    Set objOutlookRecipient = Nothing
End Sub

Open in new window

0
Comment
Question by:Galadorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 36574730
I try VERY hard to accomplish this also--but the results are inconsistent.
I even use API code in trying to bring stuff to the front.
I haven't found anything bulletproof yet.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36575256
Nick.

FWIW, I never experienced this.

Could it have anything to do with outlook not being open when the code is run?

There are a couple of techniques (I am sure you are aware of), that will open outlook, if it is not already open...

Waddaya think?

Jeff
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36575380
I recycle Outlook if it's open, and open it if it is not open
 
Private Sub CreateAnEmail(BodyText As String, SuccessfulAttachment As Boolean)


On Error Resume Next
Dim db As Database
Dim rs As Recordset
Dim ClientEmail As String
Dim DisplayMsg As Boolean
Dim AttachmentPath As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objOutlookExplorers As Outlook.Explorers
Dim myarray() As String
Dim myaddresses() As String
Dim x As Integer
Dim fs As Object
Dim BuiltPath As String
Dim response As Integer
Dim WasOpen As Boolean

Do While GetPrinterDetails("Adobe PDF").Jobs > 0
DoEvents
Loop



DisplayMsg = True

AttachmentPath = "U:\computer2\ISO Files\Cert Tracking\" & Format(Now(), "dd-mmm-yyyy") & " Weekly Cert Submitted Summary.xls"
Set objOutlook = GetObject(, "Outlook.Application")
'MsgBox Err.Number & " " & Err.Description
If Err.Number = 429 Then
    Err.Clear
    WasOpen = False
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
Else
    WasOpen = True
End If
    Dim ns As Outlook.NameSpace
    Dim Folder As Outlook.MAPIFolder
    Set ns = objOutlook.GetNamespace("MAPI")
    Set Folder = ns.GetDefaultFolder(olFolderInbox)
    Set objOutlookExplorers = objOutlook.Explorers

If WasOpen = False Then
    objOutlook.Explorers.Add Folder
    Folder.Display
    'done opening
End If


' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

    Set objOutlookRecip = .Recipients.Add("This dude")
    objOutlookRecip.Type = olTo

    ' Add the CC recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add(fGetFullNameOfLoggedUser())
    objOutlookRecip.Type = olCC

    ' Add the CC recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add("Another dude")
    objOutlookRecip.Type = olCC

   ' Add the BCC recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add("Somebody else")
    objOutlookRecip.Type = olBCC
   ' Add the BCC recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add("and her")
   objOutlookRecip.Type = olBCC

   ' Set the Subject, Body, and Importance of the message.
   .Subject = "Weekly Cert Completion Stats"
   .Body = BodyText
   .Importance = olImportanceHigh  'High importance

   ' Add attachments to the message.
   'reportcaption is passed in and split into myarray
   'one attachment needs to be added for each element in myarray


    'AttachmentPath = "c:\tempPDF\" & myarray(x) & ".pdf"
    If SuccessfulAttachment = True Then
        Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    End If


   ' Resolve each Recipient's name.
   For Each objOutlookRecip In .Recipients
       objOutlookRecip.Resolve
   Next

   ' Should we display the message before sending?
   If DisplayMsg Then
       .Display
   Else
       '.Save
       '.Send
   End If
End With




Set objOutlook = Nothing

End Sub

Open in new window

And yet sometimes the email security message hides underneath, and other times it pops over
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Galadorn
ID: 36576241
Each time I saw this behavior, Outlook was already opened. But I have to try 10-20 times to see the problem occuring.

Can I set focus on the new mail message when I'm onCurrent event on my form ? Or somewhere else...
Don't kown how to do and if it'll solve the problem.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 36576465
I have seen this occur and I have tried mightily to cut it off at the knees.
The Access VBA calls the Outlook, and you can set Outlook's focus to be whatever.
It is very much harder to tell Access that it will no longer be the active application of the operating system and that Outlook will be.
I have created API code to do that, but even then, results are uneven.

The best I have been able to do is throw a message box before the Outlook code begins to execute warning the user it may play hide-and-seek and look for Outlook on the Windows taskbar if it does.  90% of the time it's good and Outlook becomes the active application.  10% of the time, it doesn't happen.

It doesn't seem to matter if Outlook was open or not, minimized, restored or maximized--most times it works, sometimes it doesn't.
It is helpful if your VBA code stops after it does it's Outlook stuff
I.E.
Do some Access stuff, open Outlook, do more Access stuff tends to not bring Outlook to the fore
Do some Access stuff, open Outlook and do Outlook stuff and end tends to work better.

But again, I don't have it working consistently
0
 

Author Closing Comment

by:Galadorn
ID: 36928657
Ok thanks for your help.
I've followed your advice and stop all vba code after the opening of the new mail message and it seems to reduce the problem to the maximum.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question