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