Original post:Declaring API functions for 64 bit Office (and Mac Office)

Content

• Introduction
• Links
• Declarations by API function
• Which Longs should become LongPtr?
• Conditional compiling
• Other API functions

Introduction

If you develop VBA code for multiple versions of Office, you may face a challenge: ensuring your code works on both 32 bit and 64 bit platforms.

This page is meant to be the first stop for anyone who needs the proper syntax for his API declaration statement in Office VBA.

Many of the declarations were figured out by Charles Williams of www.decisionmodels.com when he created the 64 bit version of our Name Manager.

All of these are Windows API calls. Some have Mac equivalents however (like the CopyMemory one). I’ll try to add those as I find them.

Links

Of course Microsoft documents how to do this. There is an introductory article on Microsoft MSDN:

Compatibility Between the 32-bit and 64-bit Versions of Office 2010

That article describes the how-to’s to properly write the declarations. What is missing is which type declarations go with which API function or sub.

Microsoft has provided an updated version of the Win32API.txt with all proper declarations available for download here:

Office 2010 Help Files: Win32API_PtrSafe with 64-bit Support

When you run the installer after downloading the file form the link above, it does not tell you where it installed the information. Look in this -new- folder on your C drive:

C:\Office 2010 Developer Resources\Documents\Office2010Win32API_PtrSafe

You can find a list of the old Win32 API declarations here:

Visual Basic Win32 API Declarations

Microsoft also published a tool to check your code for 64 bit related problems, called the Microsoft Office Code Compatibility inspector addin.

API functions that were added/modified in 64-bit Windows: http://msdn.microsoft.com/en-us/library/aa383663(VS.85).aspx

API Functions by Windows release:

 http://msdn.microsoft.com/en-us/library/aa383687(VS.85).aspx

Utter Access API declarations (a comprehensive list of many declarations)

Last, but certainly not least: Dennis Walentin has built an API viewer that is really helpful. You can find the API viewer here.

Declarations by API function

CloseClipboard

#If VBA7 Then
Declare PtrSafe Function CloseClipboard Lib “User32” () As LongPtr
#Else
DeclareFunction CloseClipboard Lib “User32” () AsLong
#End If

CopyMemory

#If Mac Then
PrivateDeclare PtrSafe Function CopyMemory_byVar Lib “libc.dylib” Alias “memmove” (ByRef dest As Any, ByRef src As Any, ByVal size AsLong) As LongPtr
#Else
    #If VBA7 Then
PublicDeclare PtrSafe Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” _
               (ByRef destination As Any, ByRef SOURCE As Any, ByVal Length As LongPtr)
    #Else
PublicDeclareSub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” _
                                      (ByRef destination As Any, ByRef SOURCE As Any, ByVal Length AsLong)
    #End If
#End If

CreateProcess

This is a complicated one because it has a lot of arguments. A fully functional example is included below the example declaration lines.
Courtesy: The example code was taken from this page

‘Full example shown below, including the necessary structures
#If VBA7 Then
Declare PtrSafe Function CreateProcess Lib “kernel32” _
                                   Alias “CreateProcessA” (ByVal lpApplicationName AsString, _
ByVal lpCommandLine AsString, _
                                                           lpProcessAttributes As SECURITY_ATTRIBUTES, _
                                                           lpThreadAttributes As SECURITY_ATTRIBUTES, _
ByVal bInheritHandles AsLong, _
ByVal dwCreationFlags AsLong, _
                                                           lpEnvironment As Any, _
ByVal lpCurrentDriectory AsString, _
                                                           lpStartupInfo As STARTUPINFO, _
                                                           lpProcessInformation As PROCESS_INFORMATION) As LongPtr

Const INFINITE = &HFFFF
Const STARTF_USESHOWWINDOW = &H1
PrivateEnum enSW
    SW_HIDE = 0
    SW_NORMAL = 1
    SW_MAXIMIZE = 3
    SW_MINIMIZE = 6
EndEnum

PrivateType PROCESS_INFORMATION
    hProcess AsLongPtr
    hThread AsLongPtr
    dwProcessId AsLong
    dwThreadId AsLong
EndType

PrivateType STARTUPINFO
    cb AsLong
    lpReserved AsString
    lpDesktop AsString
    lpTitle AsString
    dwX AsLong
    dwY AsLong
    dwXSize AsLong
    dwYSize AsLong
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute AsLong
    dwFlags AsLong
    wShowWindow AsInteger
    cbReserved2 AsInteger
    lpReserved2 AsByte
    hStdInput As LongPtr
    hStdOutput As LongPtr
    hStdError As LongPtr
EndType

PrivateType SECURITY_ATTRIBUTES
    nLength As Long
    lpSecurityDescriptor AsLongPtr
    bInheritHandle AsLong
EndType

PrivateEnum enPriority_Class
    NORMAL_PRIORITY_CLASS = &H20
    IDLE_PRIORITY_CLASS = &H40
    HIGH_PRIORITY_CLASS = &H80
EndEnum
#Else
DeclareFunction CreateProcess Lib “kernel32” _
                                   Alias “CreateProcessA” (ByVal lpApplicationName AsString, _
ByVal lpCommandLine AsString, _
                                                           lpProcessAttributes As SECURITY_ATTRIBUTES, _
                                                           lpThreadAttributes As SECURITY_ATTRIBUTES, _
ByVal bInheritHandles AsLong, _
ByVal dwCreationFlags AsLong, _
                                                           lpEnvironment As Any, _
ByVal lpCurrentDriectory AsString, _
                                                           lpStartupInfo As STARTUPINFO, _
                                                           lpProcessInformation As PROCESS_INFORMATION) AsLong

Const INFINITE = &HFFFF
Const STARTF_USESHOWWINDOW = &H1
PrivateEnum enSW
    SW_HIDE = 0
    SW_NORMAL = 1
    SW_MAXIMIZE = 3
    SW_MINIMIZE = 6
EndEnum

PrivateType PROCESS_INFORMATION
    hProcess AsLong
    hThread AsLong
    dwProcessId AsLong
    dwThreadId AsLong
EndType

PrivateType STARTUPINFO
    cb AsLong
    lpReserved AsString
    lpDesktop AsString
    lpTitle AsString
    dwX AsLong
    dwY AsLong
    dwXSize AsLong
    dwYSize AsLong
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute AsLong
    dwFlags AsLong
    wShowWindow AsInteger
    cbReserved2 AsInteger
    lpReserved2 AsByte
    hStdInput AsLong
    hStdOutput AsLong
    hStdError AsLong
EndType

PrivateType SECURITY_ATTRIBUTES
    nLength AsLong
    lpSecurityDescriptor AsLong
    bInheritHandle As Long
EndType

PrivateEnum enPriority_Class
    NORMAL_PRIORITY_CLASS = &H20
    IDLE_PRIORITY_CLASS = &H40
    HIGH_PRIORITY_CLASS = &H80
EndEnum
#End If

PrivateFunction SuperShell(ByVal App AsString, ByVal WorkDir AsString, dwMilliseconds AsLong, _
ByVal start_size As enSW, ByVal Priority_Class As enPriority_Class) AsBoolean


Dim pclass AsLong
Dim sinfo As STARTUPINFO
Dim pinfo As PROCESS_INFORMATION
‘Not used, but needed
Dim sec1 As SECURITY_ATTRIBUTES
Dim sec2 As SECURITY_ATTRIBUTES
‘Set the structure size
    sec1.nLength = Len(sec1)
    sec2.nLength = Len(sec2)
    sinfo.cb = Len(sinfo)
‘Set the flags
    sinfo.dwFlags = STARTF_USESHOWWINDOW
‘Set the window’s startup position
    sinfo.wShowWindow = start_size
‘Set the priority class
    pclass = Priority_Class

‘Start the program
If CreateProcess(vbNullString, App, sec1, sec2, False, pclass, _
                     0&, WorkDir, sinfo, pinfo) Then
‘Wait
‘ WaitForSingleObject pinfo.hProcess, dwMilliseconds
        SuperShell = True
Else
        SuperShell = False
EndIf
EndFunction

Sub Test()
Dim sFile AsString
‘Set the dialog’s title
    sFile = Application.GetOpenFilename(“Executables (*.exe), *.exe”, , “”)
    SuperShell sFile, Left(sFile, InStrRev(sFile, “\”)), 0, SW_NORMAL, HIGH_PRIORITY_CLASS
EndSub

DrawMenuBar

#If VBA7 Then
PrivateDeclare PtrSafe Function DrawMenuBar Lib “user32” (ByVal hWnd As LongPtr) AsLong
#Else
PrivateDeclareFunction DrawMenuBar Lib “user32” (ByVal hWnd AsLong) As Long
#End If

EmptyClipboard

#If VBA7 Then
    Declare PtrSafe Function EmptyClipboard Lib “User32” () AsLongPtr
#Else
DeclareFunction EmptyClipboard Lib “User32” () As Long
#End If

FindWindow

#If VBA7 Then
PrivateDeclare PtrSafe Function FindWindow Lib “USER32” Alias “FindWindowA” (ByVal lpClassName AsString, ByVal lpWindowName AsString) As LongPtr
#Else
Private Declare Function FindWindow Lib “USER32” Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End If

FindWindowEx

#If VBA7 Then
    PrivateDeclare PtrSafe Function FindWindowEx Lib “USER32” _
                                  Alias “FindWindowExA” (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, _
ByVal lpsz1 AsString, ByVal lpsz2 AsString) As LongPtr
#Else
    PrivateDeclareFunction FindWindowEx Lib “USER32” _
                                  Alias “FindWindowExA” (ByVal hWnd1 AsLong, ByVal hWnd2 AsLong, _
ByVal lpsz1 AsString, ByVal lpsz2 AsString) AsLong
#End If

GdipCreateBitmapFromFile

#If VBA7 Then
PrivateDeclare PtrSafe Function GdipCreateBitmapFromFile Lib “GDIPlus” (ByVal filename As LongPtr, bitmap As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GdipCreateBitmapFromFile Lib “GDIPlus” (ByVal filename AsLong, bitmap AsLong) As Long
#End If

GdipCreateHBITMAPFromBitmap

#If VBA7 Then
PrivateDeclare PtrSafe Function GdipCreateHBITMAPFromBitmap Lib “GDIPlus” (ByVal bitmap As LongPtr, hbmReturn AsLongPtr, ByVal background As Long) AsLongPtr
#Else
PrivateDeclareFunction GdipCreateHBITMAPFromBitmap Lib “GDIPlus” (ByVal bitmap AsLong, hbmReturn AsLong, ByVal background AsLong) As Long
#End If

GdipDisposeImage

#If VBA7 Then
PrivateDeclare PtrSafe Function GdipDisposeImage Lib “GDIPlus” (ByVal image As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GdipDisposeImage Lib “GDIPlus” (ByVal image AsLong) As Long
#End If

GdiplusShutdown

#If VBA7 Then
PrivateDeclare PtrSafe Function GdiplusShutdown Lib “GDIPlus” (ByVal token As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GdiplusShutdown Lib “GDIPlus” (ByVal token AsLong) As Long
#End If

GdiplusStartup

#If VBA7 Then
PrivateDeclare PtrSafe Function GdiplusStartup Lib “GDIPlus” (token As LongPtr, inputbuf As GdiplusStartupInput, OptionalByVal outputbuf As LongPtr = 0) AsLongPtr

PrivateType GdiplusStartupInput
        GdiplusVersion As Long
        DebugEventCallback AsLongPtr
        SuppressBackgroundThread AsLong
        SuppressExternalCodecs AsLong
EndType
#Else
PrivateDeclareFunction GdiplusStartup Lib “GDIPlus” (token AsLong, inputbuf As GdiplusStartupInput, OptionalByVal outputbuf AsLong = 0) AsLong
PrivateType GdiplusStartupInput
        GdiplusVersion AsLong
        DebugEventCallback AsLong
        SuppressBackgroundThread AsLong
        SuppressExternalCodecs As Long
EndType

#End If

GetClassName

#If VBA7 Then
PrivateDeclare PtrSafe Function GetClassName Lib “user32” Alias “GetClassNameA” _
           (ByVal hWnd As LongPtr, ByVal lpClassName AsString, _
ByVal nMaxCount As LongPtr) AsLong
#Else
PrivateDeclareFunction GetClassName Lib “user32” Alias “GetClassNameA” _
                                         (ByVal hWnd AsLong, ByVal lpClassName AsString, _
ByVal nMaxCount AsLong) As Long
#End If

GetDiskFreeSpaceEx

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDiskFreeSpaceEx Lib “kernel32” Alias _
            “GetDiskFreeSpaceExA” (ByVal lpDirectoryName AsString, _
                                   lpFreeBytesAvailableToCaller AsCurrency, lpTotalNumberOfBytes As _
Currency, lpTotalNumberOfFreeBytes AsCurrency) As LongPtr
#Else
PrivateDeclareFunction GetDiskFreeSpaceEx Lib “kernel32” _
                                                Alias “GetDiskFreeSpaceExA” (ByVal lpDirectoryName AsString, _
                                                                             lpFreeBytesAvailableToCaller AsCurrency, _
                                                                             lpTotalNumberOfBytes As Currency, _
                                                                             lpTotalNumberOfFreeBytes AsCurrency) AsLong
#End If

GetDC

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDC Lib “user32” (ByVal hWnd As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GetDC Lib “user32” (ByVal hWnd AsLong) As Long
#End If

GetDesktopWindow

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDesktopWindow Lib “user32” () As LongPtr
#Else
PrivateDeclareFunction GetDesktopWindow Lib “user32” () AsLong
#End If

getDeviceCaps

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDeviceCaps Lib “gdi32” (ByVal hDC As LongPtr, ByVal nIndex AsLong) AsLong
#Else
PrivateDeclareFunction GetDeviceCaps Lib “gdi32” (ByVal hDC AsLong, ByVal nIndex AsLong) AsLong
#End If

GetDriveType

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDriveType Lib “kernel32” Alias _
            “GetDriveTypeA” (ByVal sDrive AsString) As LongPtr
#Else
PrivateDeclareFunction GetDriveType Lib “kernel32” Alias _
                                          “GetDriveTypeA” (ByVal sDrive AsString) AsLong
#End If

GetExitCodeProcess

#If VBA7 Then
Declare PtrSafe Function GetExitCodeProcess Lib “kernel32” (ByVal _
        hProcess As LongPtr, lpExitCode As Long) AsLong
#Else
DeclareFunction GetExitCodeProcess Lib “kernel32” (ByVal _
        hProcess AsLong, lpExitCode AsLong) As Long
#End If

GetForegroundWindow

#If VBA7 Then
Declare PtrSafe Function GetForegroundWindow Lib “user32.dll” () As LongPtr
#Else
DeclareFunction GetForegroundWindow Lib “user32.dll” () AsLong
#End If

GetFrequency

#If VBA7 Then
PrivateDeclare PtrSafe Function GetFrequency Lib “kernel32” Alias “QueryPerformanceFrequency” (cyFrequency AsCurrency) AsLong
#Else
PrivateDeclareFunction GetFrequency Lib “kernel32” Alias “QueryPerformanceFrequency” (cyFrequency AsCurrency) AsLong
#End If

GetKeyState

#If VBA7 Then
Declare PtrSafe Function GetKeyState Lib “USER32” (ByVal vKey AsLong) AsInteger
#Else
DeclareFunction GetKeyState Lib “USER32” (ByVal vKey AsLong) AsInteger
#End If

GetLastInputInfo

#If VBA7 Then
PrivateType LASTINPUTINFO
        cbSize As LongPtr
        dwTime As LongPtr
EndType
PrivateDeclare PtrSafe Sub GetLastInputInfo Lib “USER32” (ByRef plii As LASTINPUTINFO)
#Else
PrivateType LASTINPUTINFO
        cbSize AsLong
        dwTime AsLong
EndType
PrivateDeclareSub GetLastInputInfo Lib “USER32” (ByRef plii As LASTINPUTINFO)
#End If

GetOpenFileName

OptionExplicit

#If VBA7 Then
PrivateDeclare PtrSafe Function GetOpenFileName Lib “comdlg32.dll” Alias _
            “GetOpenFileNameA” (pOpenfilename As OPENFILENAME) AsLong

PrivateType OPENFILENAME
        lStructSize AsLong
        hwndOwner AsLongPtr
        hInstance AsLongPtr
        lpstrFilter AsString
        lpstrCustomFilter AsString
        nMaxCustFilter AsLong
        nFilterIndex AsLong
        lpstrFile AsString
        nMaxFile AsLong
        lpstrFileTitle AsString
        nMaxFileTitle AsLong
        lpstrInitialDir AsString
        lpstrTitle AsString
        flags AsLong
        nFileOffset AsInteger
        nFileExtension AsInteger
        lpstrDefExt AsString
        lCustData AsLongPtr
        lpfnHook AsLongPtr
        lpTemplateName AsString
EndType

#Else

PrivateDeclareFunction GetOpenFileName Lib “comdlg32.dll” Alias _
            “GetOpenFileNameA” (pOpenfilename As OPENFILENAME) AsLong

PrivateType OPENFILENAME
        lStructSize AsLong
        hwndOwner AsLong
        hInstance AsLong
        lpstrFilter AsString
        lpstrCustomFilter AsString
        nMaxCustFilter AsLong
        nFilterIndex AsLong
        lpstrFile AsString
        nMaxFile AsLong
        lpstrFileTitle AsString
        nMaxFileTitle AsLong
        lpstrInitialDir AsString
        lpstrTitle AsString
        flags AsLong
        nFileOffset AsInteger
        nFileExtension AsInteger
        lpstrDefExt AsString
        lCustData AsLong
        lpfnHook AsLong
        lpTemplateName AsString
EndType
#End If
‘/////////////////////////////////
‘// End code GetOpenFileName    //
‘/////////////////////////////////


PrivateFunction GetMyFile(strTitle AsString) AsString

Dim OpenFile    As OPENFILENAME
Dim lReturn     AsLong

    OpenFile.lpstrFilter = “”
    OpenFile.nFilterIndex = 1
    OpenFile.hwndOwner = 0
    OpenFile.lpstrFile = String(257, 0)
    #If VBA7 Then
        OpenFile.nMaxFile = LenB(OpenFile.lpstrFile) – 1
        OpenFile.lStructSize = LenB(OpenFile)
    #Else
        OpenFile.nMaxFile = Len(OpenFile.lpstrFile) – 1
        OpenFile.lStructSize = Len(OpenFile)
    #EndIf
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = “C:\”
    OpenFile.lpstrTitle = strTitle
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)

If lReturn = 0 Then
        GetMyFile = “”
Else
        GetMyFile = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) – 1))
EndIf

EndFunction

GetSystemMetrics

#If VBA7 Then
PrivateDeclare PtrSafe Function GetSystemMetrics Lib “user32” (ByVal nIndex AsLong) AsLong
#Else
PrivateDeclareFunction GetSystemMetrics Lib “user32” (ByVal nIndex AsLong) AsLong
#End If

GetTempPath

#If VBA7 Then
PrivateDeclare PtrSafe Function GetTempPath Lib “kernel32” _
            Alias “GetTempPathA” (ByVal nBufferLength As LongPtr, _
ByVal lpbuffer AsString) AsLong
#Else
PrivateDeclareFunction GetTempPath Lib “kernel32” _
                                         Alias “GetTempPathA” (ByVal nBufferLength AsLong, _
ByVal lpbuffer AsString) AsLong
#End If

getTickCount

#If VBA7 Then
PrivateDeclare PtrSafe Function getTickCount Lib “kernel32” Alias “QueryPerformanceCounter” (cyTickCount AsCurrency) AsLong
#Else
PrivateDeclareFunction getTickCount Lib “kernel32” Alias “QueryPerformanceCounter” (cyTickCount AsCurrency) AsLong
#End If

timeGetTime

#If VBA7 Then
PrivateDeclare PtrSafe Function timeGetTime Lib “winmm.dll” () AsLong
#Else
PrivateDeclareFunction timeGetTime Lib “winmm.dll” () AsLong
#End If

GetWindow

#If VBA7 Then
PrivateDeclare PtrSafe Function GetWindow Lib “user32” _
            (ByVal hWnd As LongPtr, ByVal wCmd AsLong) As LongPtr
#Else
PrivateDeclareFunction GetWindow Lib “user32” _
                                       (ByVal hWnd AsLong, ByVal wCmd AsLong) AsLong
#End If

GetWindowLong

This is one of the few API functions that requires the Win64 compile constant:

#If VBA7 Then
    #If Win64 Then
PrivateDeclare PtrSafe Function GetWindowLongPtr Lib “USER32” Alias “GetWindowLongPtrA” (ByVal hWnd As LongPtr, ByVal nIndex AsLong) As LongPtr
    #Else
    PrivateDeclare PtrSafe Function GetWindowLongPtr Lib “USER32” Alias “GetWindowLongA” (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #End If
#Else
PrivateDeclareFunction GetWindowLong Lib “USER32” Alias “GetWindowLongA” (ByVal hWnd As Long, ByVal nIndex AsLong) As Long
#End If

GetWindowsDirectory

#If VBA7 Then
PrivateDeclare PtrSafe Function GetWindowsDirectory& Lib “kernel32” Alias _
            “GetWindowsDirectoryA” (ByVal lpbuffer AsString, _
ByVal nSize As LongPtr)
#Else
PrivateDeclareFunction GetWindowsDirectory& Lib “kernel32” Alias _
            “GetWindowsDirectoryA” (ByVal lpbuffer AsString, _
ByVal nSize AsLong)
#End If

GetWindowText

#If VBA7 Then
PrivateDeclare PtrSafe Function GetWindowText Lib “user32” Alias “GetWindowTextA” _
            (ByVal hWnd As LongPtr, ByVal lpString As String, _
ByVal cch As LongPtr) AsLong
#Else
PrivateDeclareFunction GetWindowText Lib “user32” Alias “GetWindowTextA” _
                                           (ByVal hWnd AsLong, ByVal lpString As String, _
ByVal cch AsLong) As Long
#End If

GetWindowTextLength

#If VBA7 Then
PrivateDeclare PtrSafe Function GetWindowTextLength Lib “user32” Alias “GetWindowTextLengthA” _
            (ByVal hWnd As LongPtr) AsLong
#Else
PrivateDeclareFunction GetWindowTextLength Lib “user32” Alias “GetWindowTextLengthA” _
                                           (ByVal hWnd AsLong) As Long
#End If

GlobalAlloc

#If VBA7 Then
PrivateDeclare PtrSafe Function GlobalAlloc Lib “kernel32” (ByVal wFlags AsLong, ByVal dwBytes As LongPtr) As LongPtr
#Else
PrivateDeclareFunction GlobalAlloc Lib “kernel32” (ByVal wFlags AsLong, ByVal dwBytes AsLong) AsLong
#End If

GlobalLock

#If VBA7 Then
PrivateDeclare PtrSafe Function GlobalLock Lib “kernel32” (ByVal hMem As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction GlobalLock Lib “kernel32” (ByVal hMem AsLong) As Long
#End If

InternetGetConnectedState

#If VBA7 Then
PrivateDeclare PtrSafe Function InternetGetConnectedState _
Lib “wininet.dll” (lpdwFlags As LongPtr, _
ByVal dwReserved AsLong) AsBoolean
#Else
PrivateDeclareFunction InternetGetConnectedState _
Lib “wininet.dll” (lpdwFlags AsLong, _
ByVal dwReserved AsLong) AsBoolean
#End If

IsCharAlphaNumericA

#If VBA7 Then
PrivateDeclare PtrSafe Function IsCharAlphaNumericA Lib “user32” (ByVal byChar AsByte) AsLong
#Else
PrivateDeclareFunction IsCharAlphaNumericA Lib “user32” (ByVal byChar AsByte) AsLong
#End If

lstrcpy

#If VBA7 Then
PrivateDeclare PtrSafe Function lstrcpy Lib “kernel32” (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
#Else
PrivateDeclareFunction lstrcpy Lib “kernel32” (ByVal lpString1 As Any, ByVal lpString2 As Any) AsLong
#End If

Mouse_Event

#If VBA7 Then
Private Declare PtrSafe Sub mouse_event Lib “user32” (ByVal dwFlags AsLong, ByVal dx AsLong, _
ByVal dy AsLong, ByVal cButtons AsLong, _
ByVal dwExtraInfo As LongPtr)
#Else
PrivateDeclareSub mouse_event Lib “user32” (ByVal dwFlags AsLong, ByVal dx AsLong, _
ByVal dy AsLong, ByVal cButtons AsLong, _
ByVal dwExtraInfo AsLong)
#End If
PrivateConst MOUSEEVENTF_MOVE = &H1         ‘ mouse move

OleCreatePictureIndirect

#If VBA7 Then
PrivateDeclare PtrSafe Function OleCreatePictureIndirect Lib “oleaut32.dll” (PicDesc As PICTDESC, RefIID As GUID, ByVal fPictureOwnsHandle As LongPtr, IPic As IPicture) As LongPtr

PrivateType PICTDESC
        Size AsLong
TypeAsLong
        hPic As LongPtr
        hPal AsLongPtr
EndType
#Else
PrivateDeclareFunction OleCreatePictureIndirect Lib “oleaut32.dll” (PicDesc As PICTDESC, RefIID As GUID, ByVal fPictureOwnsHandle AsLong, IPic As IPicture) AsLong

PrivateType PICTDESC
        Size AsLong
TypeAsLong
        hPic AsLong
        hPal As Long
EndType
#End If

OleTranslateColor

#If VBA7 Then
PrivateDeclare PtrSafe Function OleTranslateColor Lib “oleaut32.dll” (ByVal lOleColor AsLong, _
ByVal lHPalette AsLong, lColorRef AsLong) AsLong
#Else
PrivateDeclareFunction OleTranslateColor Lib “olepro32.dll” (ByVal lOleColor AsLong, _
ByVal lHPalette AsLong, ByRef lColorRef AsLong) AsLong
#End If

OpenClipboard

#If VBA7 Then
PrivateDeclare PtrSafe Function OpenClipboard Lib “user32” (ByVal hWnd As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction OpenClipboard Lib “user32” (ByVal hWnd AsLong) As Long
#End If

OpenProcess

#If VBA7 Then
Declare PtrSafe Function OpenProcess Lib “kernel32” (ByVal _
        dwDesiredAccess AsLong, ByVal bInheritHandle AsLong, ByVal _
        dwProcessId AsLong) As LongPtr
#Else
DeclareFunction OpenProcess Lib “kernel32” (ByVal _
        dwDesiredAccess AsLong, ByVal bInheritHandle AsLong, ByVal _
        dwProcessId AsLong) AsLong
#End If

ReleaseDC

#If VBA7 Then
PrivateDeclare PtrSafe Function ReleaseDC Lib “user32” (ByVal hWnd As LongPtr, ByVal hDC As LongPtr) AsLong
#Else
PrivateDeclareFunction ReleaseDC Lib “user32” (ByVal hWnd AsLong, ByVal hDC AsLong) As Long
#End If

SendMessage

#If VBA7 Then
PrivateDeclare PtrSafe Function SendMessageA Lib “user32” (ByVal hWnd As LongPtr, ByVal wMsg AsLong, _
ByVal wParam As LongPtr, lParam As Any) As LongPtr
#Else
PrivateDeclareFunction SendMessageA Lib “user32” (ByVal hWnd AsLong, ByVal wMsg AsLong, _
ByVal wParam AsLong, lParam As Any) AsLong
#End If

SetActiveWindow

#If VBA7 Then
PrivateDeclare PtrSafe Function SetActiveWindow Lib “user32.dll” (ByVal hWnd As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction SetActiveWindow Lib “user32.dll” (ByVal hWnd AsLong) As Long
#End If

SetClipboardData

#If VBA7 Then
PrivateDeclare PtrSafe Function SetClipboardData Lib “user32” (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) AsLongPtr
#Else
PrivateDeclareFunction SetClipboardData Lib “user32” (ByVal wFormat AsLong, ByVal hMem AsLong) As Long
#End If

SetCurrentDirectory

#If VBA7 Then
PrivateDeclare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal lpPathName AsString) AsLong
#Else
PrivateDeclareFunction SetCurrentDirectoryA Lib “kernel32” (ByVal lpPathName AsString) AsLong
#End If

SetWindowLongPtr

This is another one of the few API functions that require the Win64 compile constant:

#If VBA7 Then
    #If Win64 Then
PrivateDeclare PtrSafe Function SetWindowLongPtr Lib “USER32” Alias “SetWindowLongPtrA” (ByVal hWnd As LongPtr, ByVal nIndex AsLong, ByVal dwNewLong As LongPtr) As LongPtr
    #Else
    PrivateDeclareFunction SetWindowLongPtr Lib “USER32” Alias “SetWindowLongA” (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #End If
#Else
PrivateDeclareFunction SetWindowLong Lib “USER32” Alias “SetWindowLongA” (ByVal hWnd As Long, ByVal nIndex AsLong, ByVal dwNewLong As Long) As Long
#End If

SetWindowPos

#If VBA7 Then
PrivateDeclare PtrSafe Function SetWindowPos _
Lib “user32” (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, _
ByVal X AsLong, ByVal Y AsLong, ByVal cx AsLong, _
ByVal cy AsLong, ByVal wFlags AsLong) AsLong
#Else
PrivateDeclareFunction SetWindowPos _
Lib “user32” (ByVal hwnd AsLong, _
ByVal hWndInsertAfter AsLong, _
ByVal X AsLong, ByVal Y AsLong, _
ByVal cx AsLong, _
ByVal cy AsLong, _
ByVal wFlags AsLong) AsLong
#End If

SHBrowseForFolder

#If VBA7 Then
PrivateType BROWSEINFO
        hOwner As LongPtr
        pidlRoot AsLongPtr
        pszDisplayName AsString
        lpszTitle AsString
        ulFlags AsLong
        lpfn As LongPtr
        lParam As LongPtr
        iImage AsLong
EndType

PrivateDeclare PtrSafe Function SHBrowseForFolder Lib “shell32.dll” Alias “SHBrowseForFolderA” _
        (lpBrowseInfo As BROWSEINFO) As LongPtr
#Else
PrivateType BROWSEINFO
        hOwner AsLong
        pidlRoot AsLong
        pszDisplayName AsString
        lpszTitle AsString
        ulFlags AsLong
        lpfn AsLong
        lParam AsLong
        iImage AsLong
EndType

PrivateDeclareFunction SHBrowseForFolder Lib “shell32.dll” Alias “SHBrowseForFolderA” _
        (lpBrowseInfo As BROWSEINFO) AsLong
#End If
PrivateConst BIF_RETURNONLYFSDIRS = &H1

ShellExecute

#If VBA7 Then
PrivateDeclare PtrSafe Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” ( _
ByVal hWnd As LongPtr, ByVal lpOperation AsString, ByVal lpFile AsString, _
ByVal lpParameters AsString, ByVal lpDirectory AsString, ByVal nShowCmd AsLong) As LongPtr
#Else
PrivateDeclareFunction ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” ( _
ByVal hWnd AsLong, ByVal lpOperation AsString, ByVal lpFile AsString, _
ByVal lpParameters AsString, ByVal lpDirectory AsString, ByVal nShowCmd AsLong) AsLong

#End If

SHFileOperation

#If VBA7 Then
Type SHFILEOPSTRUCT
        hWnd As LongPtr
        wFunc As Long
        pFrom AsString
        pTo AsString
        fFlags AsInteger
        fAborted AsBoolean
        hNameMaps As Longptr
        sProgress AsString
EndType
Declare PtrSafe Function SHFileOperation Lib “shell32.dll” Alias “SHFileOperationA” _
                                     (lpFileOp As SHFILEOPSTRUCT) AsLongPtr
#Else
Type SHFILEOPSTRUCT
        hWnd AsLong
        wFunc AsLong
        pFrom AsString
        pTo AsString
        fFlags AsInteger
        fAborted AsBoolean
        hNameMaps AsLong
        sProgress AsString
EndType
DeclareFunction SHFileOperation Lib “shell32.dll” Alias “SHFileOperationA” _
                                     (lpFileOp As SHFILEOPSTRUCT) As Long
#End If

SHGetPathFromIDList

#If VBA7 Then
PrivateDeclare PtrSafe Function SHGetPathFromIDList Lib “shell32.dll” Alias “SHGetPathFromIDListA” _
            (ByVal pidl As LongPtr, ByVal pszPath AsString) AsBoolean
#Else
PrivateDeclareFunction SHGetPathFromIDList Lib “shell32.dll” Alias “SHGetPathFromIDListA” _
                                                 (ByVal pidl AsLong, ByVal pszPath AsString) AsBoolean
#End If

SHGetSpecialFolderLocation

#If VBA7 Then
PrivateDeclare PtrSafe Function SHGetSpecialFolderLocation Lib _
            “shell32.dll” (ByVal hwndOwner As LongPtr, ByVal nFolder AsLong, _
                           pidl As ITEMIDLIST) As LongPtr
PrivateType SHITEMID
        cb As LongPtr
        abID AsByte
EndType
#Else
PrivateDeclareFunction SHGetSpecialFolderLocation Lib _
            “shell32.dll” (ByVal hwndOwner AsLong, ByVal nFolder AsLong, _
                           pidl As ITEMIDLIST) AsLong
PrivateType SHITEMID
        cb AsLong
        abID AsByte
EndType
#End If
PrivateType ITEMIDLIST
    mkid As SHITEMID
EndType

timeGetTime

#If VBA7 Then
PrivateDeclare PtrSafe Function timeGetTime Lib “winmm.dll” () AsLong
#Else
PrivateDeclareFunction timeGetTime Lib “winmm.dll” () AsLong
#End If

URLDownloadToFile

#If VBA7 Then
PrivateDeclare PtrSafe Function URLDownloadToFile Lib “urlmon” _
        Alias “URLDownloadToFileA” (ByVal pCaller AsLongPtr, _
ByVal szURL AsString, ByVal szFileName AsString, _
ByVal dwReserved AsLongPtr, ByVal lpfnCB AsLongPtr) AsLong
#Else
PrivateDeclareFunction URLDownloadToFile Lib “urlmon” _
        Alias “URLDownloadToFileA” (ByVal pCaller AsLong, _
ByVal szURL AsString, ByVal szFileName AsString, _
ByVal dwReserved AsLong, ByVal lpfnCB AsLong) AsLong
#End If

Which Longs should become LongPtr?

It’s actually pretty easy to determine what requires LongPtr and what can stay as Long. The only things that require LongPtr are function arguments or return values that represent addresses in memory. This is because a 64-bit OS has a memory space that is too large to hold in a Long data type variable. Arguments or return values that represent data will still be declared Long even in 64-bit.

The SendMessage API is a good example because it uses both types:

32-bit:

PrivateDeclareFunction SendMessageA Lib “user32” (ByVal hWnd AsLong, ByVal wMsg AsLong, _
ByVal wParam AsLong, lParam As Any) AsLong

64 bit:

PrivateDeclare PtrSafe Function SendMessageA Lib “user32” (ByVal hWnd As LongPtr, ByVal wMsg AsLong, _
ByVal wParam AsLongPtr, lParam As Any) As LongPtr

The first argument -hWnd- is a window handle, which is an address in memory. The return value is a pointer to a function, which is also an address in memory. Both of these must be declared LongPtr in 64-bit VBA. The argument wMsg is used to pass data, so can be Long in both 32-bit and 64-bit.

How to determine what is a memory address and what is data? You just have to read the MSDN documentation for the API functions (the C++ version) and it will tell you. Anything called a handle, pointer, brush or any other object type will require a LongPtr in 64-bit. Anything that is strictly data can stay as Long.

Conditional compiling

If your code needs to run on both 32 bit and 64 bit Excel, then another thing to do is add conditional compilation to your VBA.

Microsoft devised two compile constants to handle this:

VBA7: True if you’re using Office 2010, False for older versions

WIN64: True if your Office installation is 64 bit, false for 32 bit.

Since the 64 bit declarations also work on 32 bit Office 2010, all you have to test for is VBA7:

#If VBA7 Then
PrivateDeclare PtrSafe Function GetDeviceCaps Lib “gdi32” (ByVal hDC As LongPtr, ByVal nIndex AsLong) AsLong
#Else
PrivateDeclareFunction GetDeviceCaps Lib “gdi32” (ByVal hDC AsLong, ByVal nIndex AsLong) AsLong
#End If

And then in the routine where this function is put to use:

#If VBA7 Then
Dim hDC As LongPtr
#Else
Dim hDC AsLong
#EndIf
Dim lDotsPerInch AsLong
‘Get the user’s DPI setting
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)

Basic Authentication
PORT = 587
Use SSL = True
Server Name = smtp.office365.com

TLS 1.2 is required.

How to set up a multifunction device or application to send email using Microsoft 365 or Office 365 also says:

Transport Layer Security (TLS): Your device must be able to use TLS version 1.2 and above.

• DatabaseMail.exe is built for .NET Framework 3.5, but you need a .NET Framework installed that supports TLS 1.2 (.NET Framework 4.5.2 or later).
• TLS 1.2 client protocol should be enabled at the machine level in Registry

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

• TLS 1.2 client protocol should be enabled for .NET Framework 4.x in the Registry

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

• An appropriate supportedRuntime should be in DatabaseMail.exe.config file, e.g.: with Microsoft .NET Framework 4.5.2 installed:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="DatabaseServerName" value="." />
    <add key="DatabaseName" value="msdb" />
  </appSettings>
  <startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
</configuration>

3. Via the Database Mail settings in SSMS, configure the sending account appropriately:

• Server name: smtp.office365.com
• Port number: 587 (preferred, or 25)
• This server requires a secure connection (SSL): must be ticked (this enables STARTTLS)
• SMTP Authentication:
  • Basic authentication (selected)
    • User name: sending_mailbox_user@your_domain.com
    • Password: your_office365_password
    • Confirm password: your_office365_password_again

Problem resolved by support team from Microsoft Developer Support for Internet Explorer.

The frame/dialog targeting problem described above is the result of a change introduced with installation of IE11 on a computer, affecting behavior of User Account Control: Use Admin Approval Mode for the built-in Administrator account setting within local security policy.

This setting can be configured using the Local Security Policy snap-in, in Local Policies / Security Options branch, under Policy item called: “User Account Control: Use Admin Approval Mode for the built-in Administrator account”.

In simple terms, the steps needed to correct the problem are:

Before installation of IE11 – no need to do anything on any Windows or IE version to get frame targeting to work under default Admin. It works even with “User Account Control: Use Admin Approval Mode …” set to Disabled.
 
After installation of IE11  – need to Enable “User Account Control: Use Admin Approval Mode for the built-in Administrator account” to get frame/dialog targeting to work for default Admin.

Confirmed the solution works with IE11 on Windows 2012R2 as well as Windows 7 SPK1, the adjustment takes effect after a restart. There is no need to make any changes to HTML pages.

https://social.msdn.microsoft.com/Forums/ie/en-US/a5c294e2-e407-491d-ba6a-b7f7edbcabaf/ie11-cant-post-form-data-to-specific-frame-or-window-dialog-opened-via-windowopen?forum=iewebdevelopment

—https://blog.it-koehler.com/en/Archive/3170

Dealing with PowerShell is a great thing, especially if you have to administer a large and dynamic organization. Let’s spend some time to learn some default use cases lot of exchange admins have to do. In this article I’m showing a various combination of different PowerShell cmdlets, with special focus in the new Exchange Online cmdlets. You can do a much more crazy stuff, but I want to show some basics.
Enough blabla, lets go.

Installation of the new PowerShell Module is quite easy, just follow the instructions on this link.
Here are my commands to install, import and connect EX ON Module:

Install-Module -Name ExchangeOnlineManagement -Scope AllUsers -Verbose -ForceImport-Module -Name ExchangeOnlineManagement -VerboseGet-Module ExchangeOnlineManagement$creds = Get-CredentialConnect-ExchangeOnline -Credential $creds #Connect-ExchangeOnline -Credential $creds -ShowBanner:$false

After connecting (if you didn’t switch off) you see the new cmdlets in PowerShell console:

As you can see, most of the cmdlets are Get-commands, so the new version at the moment is for analysis. If you want to have an overview of all cmdlets included in this module, use this oneliner.

Get-Module ExchangeOnlineManagement | Select-Object ExportedCommands -ExpandProperty ExportedCommands

After this short introduction, what can we do with this set of cmdlets? Playing around with this new cmdlets

Get-EXONMailbox references

This cmdlet is used to get information about mailbox settings, see also propertysets described in this link.

#1.#find all mailboxes with "alex" in the following attributes:#CommonName (CN),DisplayName,FirstName,LastName,AliasGet-EXOMailbox -Anr alex

#2.#using different propertysets to get special information

Get-EXOMailbox -PropertySets Quota#combine different propertysets Get-EXOMailbox -PropertySets Quota,Delivery#combine different propertysets with individual propertiesGet-EXOMailbox -PropertySets Quota,Delivery -Properties EmailAddresses,RecipientTypeDetails

#3.#find users with special attributes

#find all shared mailboxes and their delivery options

Get-EXOMailbox -PropertySets Delivery -Properties RecipientTypeDetails | Where-Object{$_.RecipientTypeDetails -eq "SharedMailbox"} | Sort-Object UserPrincipalName
#find all usermailboxes with their delivery options
Get-EXOMailbox -PropertySets Delivery -Properties RecipientTypeDetails | Where-Object{$_.RecipientTypeDetails -eq "UserMailbox"} | Sort-Object UserPrincipalName

Get-EXOCasMailbox

with “Get-EXOCasMailbox you can see for example the connections properties for your mailboxes (POP/IMAP/MAPI…), CAS = Client Access Service
Link to the propertysets

#1.
#see all mailboxes with their cas settings 
Get-EXOCasMailbox

#2.
#see important information with different propertysets
Get-EXOCasMailbox -PropertySets Minimum
#see all mailboxes IMAP and POP settings
Get-EXOCasMailbox -PropertySets Imap,pop

#3.
#see all users with POP enabled
Get-EXOCasMailbox -PropertySets IMAP,POP | Where-Object {$_.PopEnabled -eq $true}
#see all users with POP and IMAP enabled
Get-EXOCasMailbox -PropertySets IMAP,POP | Where-Object {($_.PopEnabled -eq $true) -and ($_.ImapEnabled -eq $true)}
#see all users OWA enabled
Get-EXOCasMailbox -Properties OWAEnabled| Where-Object {$_.OWAEnabled -eq $true}

Get-EXOMailboxStatistics

With this cmdlet you are able to see sizes of all mailboxes

#see mailboxsize of special user 
#UPN,emailaddress,GUID are accepted parameters
Get-EXOMailboxStatistics -Identity alexander
Get-MailboxFolderStatistics -Identity alexander | ft Folderpath,FolderSize 
#see statistics of all users
Get-EXOMailbox | Get-EXOMailboxStatistics
#see statistics of all shared mailboxes
Get-EXOMailbox | Where-Object{$_.RecipientTypeDetails -eq "SharedMailbox"} | Get-EXOMailboxStatistics
#see detailed statistics of all users
Get-EXOMailbox | Get-EXOMailboxStatistics -PropertySets All

Script to get biggest mailbox

$Mailboxstats = Get-EXOMailbox | Get-EXOMailboxStatistics 
$MailboxStats | Add-Member -MemberType ScriptProperty -Name TotalItemSizeInBytes -Value {$this.TotalItemSize -replace "(.*\()|,| [a-z]*\)", ""} 
$overview = $MailboxStats | Select-Object DisplayName, TotalItemSizeInBytes,@{Name="TotalItemSize (GB)"; Expression={[math]::Round($_.TotalItemSizeInBytes/1GB,2)}} | Sort-Object "TotalItemSize (GB)" -Descending
$overview

Script to get all mailboxes bigger than…

#greaterthan value in GB and with "." as comma
$greaterthan = "0.5"
$Mailboxstats = Get-EXOMailbox | Get-EXOMailboxStatistics
$MailboxStats | Add-Member -MemberType ScriptProperty -Name TotalItemSizeInBytes -Value {$this.TotalItemSize -replace "(.*\()|,| [a-z]*\)", ""} 
$overview = $MailboxStats | Select-Object DisplayName,TotalItemSizeInBytes,@{Name="TotalItemSizeInGB"; Expression={[math]::Round($_.TotalItemSizeInBytes/1GB,2)}} | Sort-Object "TotalItemSizeInGB" -Descending
$overview | Where-Object {$_.TotalItemSizeInGB -gt "$greaterthan"}

Script to get all mailboxes and sum of all mailbox sizes

$Mailboxstats = Get-EXOMailbox -ResultSize unlimited | Get-EXOMailboxStatistics 
$MailboxStats | Add-Member -MemberType ScriptProperty -Name TotalItemSizeInBytes -Value {$this.TotalItemSize -replace "(.*\()|,| [a-z]*\)", ""} 
$overview = $MailboxStats | Select-Object DisplayName, TotalItemSizeInBytes,@{Name="TotalItemSizeInGB"; Expression={[math]::Round($_.TotalItemSizeInBytes/1GB,2)}} | Sort-Object "TotalItemSizeInGB" -Descending
$sumtemp = ($overview | Measure-Object TotalItemSizeInGB -Sum).Sum
$sum = $sumtemp.ToString()
$sum = "Sum of all Mailboxes in GB: "+$sum+" GB"
Write-Host "$sum" -ForegroundColor Yellow
$overview

Get-EXOMailboxFolderPermission

Getting all permissions inside a mailbox

#1.
#get special permission on one folder (need to know the foldername)
Get-EXOMailboxFolderPermission -Identity alexander:\Inbox

#2.
#get folders from one mailbox with their permissions
$email = "blablabla@it-koehler.com"
$folders = (Get-EXOMailboxFolderStatistics -Identity $email)
$perm = @()
foreach($folder in $folders){#
  $fname = ($folder.Folderpath -replace '/','\') 
  $foldername = $email +":" +$fname
  $temp = Get-EXOMailboxFolderPermission -Identity "$foldername" -ErrorAction SilentlyContinue | Select-Object Identity,User,AccessRights
  $perm += $temp
}
$perm | ft 

Get-EXOMobileDeviceStatistics

Finding mobile devices connected with mailboxes

#1.
#get all mobile devices of one mailbox
Get-EXOMobileDeviceStatistics -Mailbox blablabla@it-koehler.com | ft DeviceFriendlyName,DeviceOS,DeviceID,DeviceImei,FirstSyncTime,LastSuccessSync

#2.
#get all users and show their mobile devices
$UPN = (Get-EXOMailbox -ResultSize unlimited).Userprincipalname 
foreach($user in $UPN){
  $mobiles = Get-EXOMobileDeviceStatistics -Mailbox $user -ErrorAction SilentlyContinue
  if($mobiles){
    Write-Host "User: $user" -ForegroundColor Yellow
    $mobiles | ft DeviceFriendlyName,DeviceOS,DeviceID,DeviceImei,FirstSyncTime,LastSuccessSync
  } 
}

Disconnect-ExchangeOnline

Disconnect-ExchangeOnline -Confirm:$false

There are much more possibilities with these cmdlets, if you have any questions or did some scripting on your own, write me an comment or email, we can share it here. If you liked these lines, please click on helpful. Stay tuned.