VBA Code Send/Receive

jstevens
SilverLounger
Posts: 2432
Joined: 26 Jan 2010, 16:31
Location: Southern California

VBA Code Send/Receive

Post by jstevens »

I found this bit of code to replicate the "send/receive" button. What I would like to do is have it complete the send/receive and then display a message box that the code finished. I have tried adding a "DoEvents" to the code but the message box appears before the process completes.

Code: Select all

Sub SendReceiveAllFolders()
    Dim oLook As Object
    Dim nsp As Object, objSyncs As Object, objSync As Object
    Dim i As Long

    Set oLook = GetObject(, "Outlook.Application")

    Set nsp = oLook.GetNamespace("MAPI")

    Set objSyncs = nsp.SyncObjects

    For i = 1 To objSyncs.count
        Set objSync = objSyncs.Item(i)
        objSync.Start
            
    Next
    DoEvents
    MsgBox "Finished"
End Sub
Your suggestions are appreciated.
Regards,
John

User avatar
Jay Freedman
Microsoft MVP
Posts: 1146
Joined: 24 May 2013, 15:33
Location: Warminster, PA

Re: VBA Code Send/Receive

Post by Jay Freedman »

Disclaimer: I know a lot of Word VBA, but Outlook VBA usually gets me twisted in knots.

I think you'll need to write a handler for the SyncEnd event of the objSync object, like the sample at https://msdn.microsoft.com/en-us/librar ... e.11).aspx, and display the message there.

The article apparently hasn't been rewritten since Office 2003, and I don't know whether that indicates that the event has been changed or dropped in later versions. :hairout:

jstevens
SilverLounger
Posts: 2432
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: VBA Code Send/Receive

Post by jstevens »

Jay,

Thanks for the link. I tried the suggestion and it seems straight forward enough but I'm not getting the Msgbox to display. The code should be put in a Class Module.

Could someone test this as I may be doing something wrong.

Code: Select all

Dim myOlApp As New Outlook.Application
Dim WithEvents mySync As Outlook.SyncObject

Private Sub Class_Initialize()
 Set mySync = myOlApp.Session.SyncObjects.Item(1)
    mySync.Start
End Sub

Private Sub mySync_SyncEnd()
     MsgBox "Synchronization is complete."
End Sub

Regards,
John

User avatar
HansV
Administrator
Posts: 71979
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Code Send/Receive

Post by HansV »

Do you want to run this code within Outlook itself, or from another application such as Excel?
Regards,
Hans

jstevens
SilverLounger
Posts: 2432
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: VBA Code Send/Receive

Post by jstevens »

Hans,

From within Outlook.
Regards,
John

User avatar
HansV
Administrator
Posts: 71979
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Code Send/Receive

Post by HansV »

Copy the following code into the ThisOutlookSession module (under Microsoft Outlook Objects):

Code: Select all

Public WithEvents mySync As Outlook.SyncObject

Private Sub Application_Quit()
    Set mySync = Nothing
End Sub

Private Sub Application_Startup()
    Set mySync = Session.SyncObjects.Item(1)
End Sub

Private Sub mySync_SyncEnd()
     MsgBox "Synchronization is complete."
End Sub
And copy the following macro into a standard module:

Code: Select all

Sub SendReceive()
    ThisOutlookSession.mySync.Start
End Sub
You can call this macro from a button.
Regards,
Hans

User avatar
HansV
Administrator
Posts: 71979
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Code Send/Receive

Post by HansV »

PS you have to quit and restart Outlook for the code to work.
Regards,
Hans

jstevens
SilverLounger
Posts: 2432
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: VBA Code Send/Receive

Post by jstevens »

Hans,

When I run SendReceive() the code errors out: Public WithEvents mySync As Outlook.SyncObject "Invalid attribute in Sub or Function."
Regards,
John

User avatar
HansV
Administrator
Posts: 71979
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Code Send/Receive

Post by HansV »

Did you copy the code into the existing ThisOutlookSession module? It works for me...
Regards,
Hans

jstevens
SilverLounger
Posts: 2432
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: VBA Code Send/Receive

Post by jstevens »

Hans,

Here is a screenshot of the error when I try to close the application.
EL_10.png
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 71979
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Code Send/Receive

Post by HansV »

Is there code above the Public WithEvents line? Declarations such as that one should always be above all Subs and Functions, near the top of the module.
Regards,
Hans

jstevens
SilverLounger
Posts: 2432
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: VBA Code Send/Receive

Post by jstevens »

Hans,

Good catch! Have a :chocciebar:.

There is peace in the Universe once again. :clapping:

Thanks!
Regards,
John

HansPeter
NewLounger
Posts: 1
Joined: 18 Jan 2021, 05:14

Re: VBA Code Send/Receive

Post by HansPeter »

To Hans or anyone,

Can you please share how I can setup this code to run from another application like Access?

User avatar
HansV
Administrator
Posts: 71979
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Code Send/Receive

Post by HansV »

Welcome to Eileen's Lounge!

Do the following in the Visual Basic Editor:

1) In Tools > References..,. tick the check box for Microsoft Outlook n.0 Object Library, then click OK.
n.0 depends on your version of Office:

12.0 for Office 2007
14.0 for Office 2010
15.0 for Office 2013
16/0 for Office 2016, Office 2019 and Microsoft 365

2) Select Insert > Class Module.
Name the class module clsOutlook.
Copy the following code into the class module:

Code: Select all

Option Explicit

Public WithEvents myOlApp As Outlook.Application
Public WithEvents mySync As Outlook.SyncObject

Private Sub Class_Initialize()
    Set myOlApp = New Outlook.Application
    Set mySync = myOlApp.Session.SyncObjects.Item(1)
    mySync.Start
End Sub

Private Sub Class_Terminate()
    Set mySync = Nothing
    myOlApp.Quit
End Sub

Private Sub mySync_SyncEnd()
     MsgBox "Synchronization is complete."
End Sub
3) Let's say you want to call the code from a command button on a form in an Access database.
Copy the following code into the form's module:

Code: Select all

Sub cmdSendReceive_Click()
    Dim myOutlook As clsOutlook
    Set myOutlook = New clsOutlook
End Sub
Regards,
Hans