[AccessD] vbSendMail (was: What's the best way to automaticallysendemails from an app)

Jim Dettman jimdettman at verizon.net
Sun Feb 13 10:16:48 CST 2011


 Hey thanks for posting that!  Very helpful.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Sunday, February 13, 2011 10:38 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] vbSendMail (was: What's the best way to
automaticallysendemails from an app)

I use vbSendMail as well at two customers.  One thing that does need to be
done is that each user's PC needs to have two files registered -
vbSendMail.dll and mswinsck.ocx.  To do this go into Run and enter: 

regsvr32 "C:\SendMailFiles\vbSendMail.dll" 

or whatever the full path actually is.  But over time, the IT folks forget
to do this, or for some reason a PC 'loses' the registration for these two
files, and email stops working.

Doing some internet research, I came up with the following code to do the
registration programmatically each time a user opens the app.  You'll need a
table to store the path to the folder which contains the two files.  Put the
following in a standard module call it during the opening sequence.

Note:  I have not tested this on a PC that doesn't have a System32 folder.

'----------------------------
Option Compare Database
Option Explicit

Private Declare Function Register_vbSendMail Lib "vbSendMail.dll" Alias
"DllRegisterServer" () As Long
Private Declare Function Register_mswinsck Lib "mswinsck.ocx" Alias
"DllRegisterServer" () As Long
Private Declare Function UnRegister_vbSendMail Lib "vbSendMail.dll" Alias
"DllUnregisterServer" () As Long
Private Declare Function UnRegister_mswinsck Lib "mswinsck.ocx" Alias
"DllUnregisterServer" () As Long

Public Function RegisterSMTPFiles()
1     On Error GoTo EH

          '-- The two files vbSendMail.dll and mswinsck.ocx are used to
provide support for SMTP Email. _
              The reference to vbSendMail.dll must already exist in the FE
files(s). _
              So, if the files don't exist, they will be copied, and then
will be registered, and the references will work.
          
          '-- This actual file registration occurs on each opening I don't
know how to detect if a file _
              is registered or not.  However, registering on each opening
shouldn't hurt.  If the two files already exist, _
              then the two files will be re-registered.

          Dim retCode As Long
          Dim stgPrompt As String
          Dim fso As FileSystemObject
          Dim stgmsWinSckPath As String
          Dim stgvbSendMailPath As String
          Dim stgSMTPFilesSourcePath As String
          Dim rst As DAO.Recordset
          Dim stg As String
          Dim stgFolderName As String
          
2         stg = "SELECT ServerSystemFolder FROM tblParameters"
3         Set rst = DBEngine(0)(0).OpenRecordset(stg, dbOpenSnapshot)
4         stgSMTPFilesSourcePath = rst("ServerSystemFolder") & "\SMTPFiles"
5         rst.Close
6         Set rst = Nothing
          
7         Set fso = CreateObject("Scripting.FileSystemObject")
          
          '-- Windows 7 w/o System32 folder
8         If fso.FolderExists("C:\Windows\SysWOW64") Then
9             stgmsWinSckPath = "C:\Windows\SysWOW64\mswinsck.ocx"
10            stgvbSendMailPath = "C:\Windows\SysWOW64\vbSendMail.dll"
11            stgFolderName = "C:\Windows\SysWOW64"
12        End If
          
          '-- Windows XP and Windows 7 with System32 folder
13        If fso.FolderExists("C:\Windows\System32") Then
14            stgmsWinSckPath = "C:\Windows\System32\mswinsck.ocx"
15            stgvbSendMailPath = "C:\Windows\System32\vbSendMail.dll"
16            stgFolderName = "C:\Windows\System32"
17        End If
          
          '-- Windows Previous to XP
18        If fso.FolderExists("C:\WINNT\System32") Then
19            stgmsWinSckPath = "C:\WINNT\System32\mswinsck.ocx"
20            stgvbSendMailPath = "C:\WINNT\System32\vbSendMail.dll"
21            stgFolderName = "C:\WINNT\System32"
22        End If
          
          '-- Copy files if needed
23        If fso.FileExists(stgmsWinSckPath) = False Then
24            fso.CopyFile stgSMTPFilesSourcePath & "\mswinsck.ocx",
stgmsWinSckPath
25        End If
26        If fso.FileExists(stgvbSendMailPath) = False Then
27            fso.CopyFile stgSMTPFilesSourcePath & "\vbSendMail.dll",
stgvbSendMailPath
28        End If
          
29        Set fso = Nothing
          
30        retCode = Register_vbSendMail()
      '    MsgBox "vbSendMail.dll Registered"
31        retCode = Register_mswinsck()
      '    MsgBox "mswinsck.dll Registered"
          
32        Exit Function
          
EH:
33        stgPrompt = "vbSendMail registration could not be completed." _
              & vbNewLine & vbNewLine _
              & "The files 'mswinsck.ocx' and/or 'vbSendMail.dll' appear to
be missing from the " & stgFolderName & " folder." _
              & vbNewLine & vbNewLine _
              & "Contact your System Owner.  This application will now Quit.
(Line " & Erl & ")"
34        MsgBox stgPrompt, vbCritical + vbOKOnly, "Missing Reference"
35        DoCmd.Quit

End Function
'----------------------------




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Sunday, February 13, 2011 5:36 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] CDO - RE: What's the best way to automatically
sendemails from an app

Stuart,

  I think he's talking about the little "install" that must be done with
Blat itself the first time you use it.  If memory serves though, I believe
their was a change with that where it was no longer required.  Been 4 or 5
years since I last used BLAT.

  I stick to vbSendMail now.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Friday, February 11, 2011 07:11 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] CDO - RE: What's the best way to automatically
sendemails from an app

What "install"?    Just stick the EXE or DLL anywhere appropriate and call
it.  There is no 
need to install anything.

-- 
Stuart

On 11 Feb 2011 at 13:37, Brad Marks wrote:

> my research, I did look at Blat, but I had trouble with the install
> and when I submitted a question, I did not receive a reply.  I thought
> that it was worth a little money to have good product support.
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list