How to Email separate pages of report to different EMail_Address per client

[voldatabase]

The Not For Profit (NFP) organisation for which I volunteer as Access 2010 maintenance programmer, requires to automate the sending of reminders to fellow NFP organisations (clients) on a regular basis.



The targets of those EMails change every month - viz. Org_D, Org_J, Org_M and Org_Q this month, and next month it may be Org_E and Org_R etc. - for every month of the year a different set of targets, as we request updates for our records.

I have got a report that does all this within Access and that works fine, as long as I send to printer, from where the pages can be separated and sent to the individual organisation. Trying to EMail this report from the Access toolbar, sends ALL the pages in one report to all clients - not good!!!!

I have also got a report that does all this as a Mailmerge within Word 2010 using the AccessDB data, and that also works fine, as long as I send to printer, from where the pages can be separated and sent to the individual organisation. Trying to save / export to .pdf this report, also saves ALL the pages in one file, which again has to be printed and sent to the individual organisation - not the desired result either!!!!

In both of the cases described above, I am not sure how to split this report into individual .pdf files (per page) for sending as attachments or by EMail_Address if the report can be sent directly.

My problem comes when I try to send this report as an EMail using Access VBA.

These requests contain our organisation logo and other 'pretties' in graphic format (requirement), so 'DoCmd.SendObject' (suggested by many sites for EMailing from AccessVBA) is not adequate for the task, according to the MS Access Help.

I have the following Access VBA References / Libraries loaded / enabled:-

Visual Basic for Applications
Microsoft Access 14.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
OLE Automation
Microsoft Office 14.0 Object Library
Microsoft Excel 14.0 Object Library
Microsoft Word 14.0 Object Library
Microsoft Outlook 14.0 Object Library
Microsoft Data Access Components Installed Version

I would prefer to bypass the need to interact in any way with Outlook or its contacts lists, as I am suspicious of security changes in future versions, future Outlook administrators getting too enthusiastic with security features some time down the track, ... etc.. If there was a way to send these using, preferably, Access ONLY, or Outlook or any other MS Office application, without using any 'other application' contact lists, due to ongoing maintenance issues, and any particular Outlook user (no one is immortal nor invulnerable to retrenchments etc.), I would be very interested. If needed, we may be willing to consider going outside MS Office, but only to an 'open source’ / free application, if there is something that would work.

The Outlook 2010 option:-

If I send the report as PDF attachments, (one page attachment per client and specific to each client) then how do I get Outlook to select and attach the correct file name from the multiple attachment files in a 'print to PDF folder', and match those files to the clients EMail address as supplied by the AccessDB?

Given that the targets change every month (and can expand and contract as new clients join and old ones leave), the Outlook 'Contacts' list needs to be changed each month also, in unison with the AccessDB data. How to do this from Access2010 VBA?

The "outside of the MS Office suit" option

How could this be achieved (SMTP?? - can everyone receive and read SMTP?)?

Would I still need to send the attachments or could the pages of the report be separated and emailed individually to the relevant recipient?

Can SMTP apps(?) send out the result of a "print_to_SMTP_printer" Access report, while splitting one clients data from the others - i.e. not sending all the report pages to ALL clients, where they should only be receiving one page relevant to their organisation?

All this is easy enough when sending the report to a printer, but automating this EMail process has had me stumped for the last six months!

I am not an Access (or indeed MS Office) expert but spent about a year creating an Access database for a client nearly 20 years ago (Access Version 2), so my knowledge of Access is somewhat dated & rusty, to put it mildly. I would therefore really appreciate any help - methodology, code, macro, .... etc., to accomplish this for these good people, so that they can help the local unemployed find work in our troubled rural economy.

Thanking you in anticipation

Alex

[ItsMe]

I see being able to do this.

Is it necessary to send the email with objects other than a .jpg in the body? Can you send the PDF as an attachment? Would it be OK to use HTML to send your email with a PDF attachment?

I have never included objects in the body of an HTML email but I doubt it is impossible. I do it from within Outlook. Regardless, you will need some type of Email client app. You can use Outlook, but don't need to use the Address book.

Just keep your contacts in a table in Access. Send your emails with some HTML tags in the body. Have a query help with a WHERE clause to create your PDF files. Use CDO to send your emails instead of Docmd. If you are OK with having your SMTP password in Access you won't need to leave anything in the Outbox or Sent folder in Outlook.

[voldatabase]

Thank you for your prompt reply.

Being a 'newbe' to Access and even more so to Outlook I have a few issues comprehending what is being asked and offered:-

'Objects' - not sure what these are. The logo's - two of them are in (I think) .jpg format, and unfortunately I don't think management will be too keen to remove them. There are, of course, variable fields per client / record that also have to be included in the report.
Yes we can send them as .pdf attachments, but how to separate the report, page by page, into attachments per client record?
I am not a HTML programmer so would not know how to include HTML code in the attachment or EMail.

How do I bypass the Outlook address book?

The current query is what gives me the data that is used on the printer and I am at a loss as to where the 'WHERE' command would be included in this.
What is CDO and how can it solve my problem?
I did not know that I had a SMTP password. Where would I find this in Outlook?

Unfortunately I only work on Tuesday and Thursday, so my responses will be sporadic, around those days.

Thank you once again for your efforts.

Alex

[ItsMe]

You can see some of what CDO does here
https://www.accessforums.net/access/...ils-36702.html

I have never used it to format emails that included pictures in the body. I just use it to send updates to clients, informal stuff. I can't imagine it would be too difficult to put some jpg's in the body. The only reason I mention it is because you don't want to be dependent on Outlook.

Using the Docmd has a lot of options that will handle what you are trying to do here. I just don't have a lot expertise in the Docmd for Emailing. I am researching it though, because I am going to weigh the differences between that an CDO. Ultimately, Docmd is probably going to need a user profile within an Outlook program. Something it sounds like you are trying to avoid.

I think you need to get started with getting the where clause figured out. You need to "Filter" your report. You should have a report that opens only the record(s) that are relevant. So you will need to query the report and include a Where clause.

It could look as simple as

strWhere = "[ClentID] =" & lngClientID

here you are matching a Client ID that a user selected somehow (Maybe from a combo box) to a query's Client ID. If your report is bound to this SELECT query, then your done. You have your WHERE clause. All you need to do now is open it as a Formatted PDF, using the WHERE clause.

You can save the PDF to disc and attach it to your email.

And of course there is more than one way to do it. When you get a chance you can post your Report's query here. Look at the query and switch to SQL view. Then copy and paste here in this thread. Then in plain English explain some fields you want to apply specific criteria to. Then we can help you get your WHERE clause together.

[ItsMe]

You can export your Outlook contacts to an Access data table if you do not already have them in a table somewhere.

[voldatabase]

Thank you for your responses.

Herewith the SQL query that runs perfectly when printing to hard-copy.

SQL Code Start

SELECT DISTINCT TblOrganisations.OrganisationID, TblOrganisations.OrgWithdrawn, TblOrganisations.OrganisationName, TblRegistration.PersonalAccidentExpiryDate, TblRegistration.PublicLiabilityExpiryDate, TblOrganisations.EmailAddress, TblOrganisations.VMFirstName, TblOrganisations.VMLastName, TblOrganisations.AddressLine1, TblOrganisations.AddressLine2, tblPostCode.Pcode, TblState.StateName
FROM TblDefaultSettings, (tblMemberType INNER JOIN (TblLGA INNER JOIN (((tblPostCode INNER JOIN (TblOrganisations INNER JOIN TblRegistration ON TblOrganisations.OrganisationID = TblRegistration.OrganisationID) ON (tblPostCode.ID = TblOrganisations.LocalityID) AND (tblPostCode.ID = TblOrganisations.OtherLocalityID)) INNER JOIN TblState ON tblPostCode.StateID = TblState.StateID) INNER JOIN TblRegions ON tblPostCode.RegionID = TblRegions.RegionID) ON TblLGA.LGAID = tblPostCode.LGAID) ON tblMemberType.MTP_Type = TblOrganisations.Type) INNER JOIN TblPaymentLog ON TblOrganisations.OrganisationID = TblPaymentLog.OrganisationID
GROUP BY TblOrganisations.OrganisationID, TblOrganisations.OrgWithdrawn, TblOrganisations.OrganisationName, TblRegistration.PersonalAccidentExpiryDate, TblRegistration.PublicLiabilityExpiryDate, TblOrganisations.EmailAddress, TblOrganisations.VMFirstName, TblOrganisations.VMLastName, TblOrganisations.AddressLine1, TblOrganisations.AddressLine2, tblPostCode.Pcode, TblState.StateName, TblPaymentLog.PaymentDate, TblDefaultSettings.OrgExpiry, Month([TblRegistration]![PersonalAccidentExpiryDate]), Month([TblRegistration]![PublicLiabilityExpiryDate]), Year([TblRegistration]![PersonalAccidentExpiryDate]), Year([TblRegistration]![PublicLiabilityExpiryDate]), TblOrganisations.LocalityID
HAVING (((TblOrganisations.OrgWithdrawn)=False) AND ((Month([TblRegistration]![PersonalAccidentExpiryDate]))=Month(DateAdd("m",1,Now()))) AND ((Year([TblRegistration]![PersonalAccidentExpiryDate]))<=Year(Now())) AND ((Last(TblPaymentLog.ExpiryDate))>Now()-[OrgExpiry])) OR (((TblOrganisations.OrgWithdrawn)=False) AND ((Month([TblRegistration]![PublicLiabilityExpiryDate]))=Month(DateAdd("m",1,Now()))) AND ((Year([TblRegistration]![PublicLiabilityExpiryDate]))<=Year(Now())))
ORDER BY TblOrganisations.OrganisationName;

SQL Code End

The problem comes about when I try to save / export the resultant report to separate .pdf by OrganisationID / EmailAddress.

I cannot find any CDO in Access VBA References / Libraries. I believe it must be installed on our Exchange server. I need to get some answers from IT regarding whether CDO is installed before I can try any of these newly suggested methods.

Thank you once again

Alex

[ItsMe]

I will take a look at your SQL. SQL is not one of my strong points. But all I am looking for is something to create a WHERE clause for your PDF files.

As for CDO. You don't need anything special. In fact, I just got it to work without an Email client installed. I started with a blank acddb and also with a blank MDB. Both versions of access had no problem without any special references. I even ran it from an XP pro machine.

Below, I pasted the code I used. I just placed it in a click a event. You will need to place your SMTP server info in the different schema lines. You can use NSLookup to resolve the server name (address) if the text description does not work. I used the actual IP address here. PLace your password in there. The password and the server IP address is the only thing you need to test it.

Other than that, make sure you have a folder and a text file on your C drive with the correct name. You need a folder named "Test" and a plain .txt file named "Test" inside that folder, if you use the attachment code.

Dim strDirectory As String
strDirectory = "C:\Test\Test.txt"
Dim strTo As String
strTo = "Email@TestEmail.com"
Dim strSubject As String
strSubject = "Subject Line for Test"
Dim strInfo As String
strInfo = " Please find the attached Test file."
Dim iCfg As Object
Dim iMsg As Object

            
            Set iCfg = CreateObject("CDO.Configuration")
            
            Set iMsg = CreateObject("CDO.Message")
            
            With iCfg.Fields
            
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "74.125.239.100"
            
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
            
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Automated@MyDomain.com"
            
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Password"
            
            .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "Do Not Reply <Automated@MyDomain.com>"
            
            .Update
            
            End With
            
             
            
            With iMsg
            
            .Configuration = iCfg
            
            .Subject = strSubject
            
            .To = strTo
            .TextBody = strInfo
            .AddAttachment strDirectory
            
            .Send
            
            End With
            
            Set iMsg = Nothing
            
            Set iCfg = Nothing

[ItsMe]

If you want to quickly try out some code that will produce a PDF from a filtered form you can paste the following into a click event.

Create a new form based on your query. Grab an email address or a Client Id and filter selecting only that email. Then click the button you created. You will see a MsgBox that will display what you have filtered. That is the ehere clause that will filter your PDF. It will not necesarily be perfect. YOu will want to make adjustments to have it equal a combobox in a form or fully automate by having a client flagged in a table to recieve the email.

YOu will be prompted to save the PDF. You can automate that too. You can save it to disc and then have the email program grab te newsletter and email it.

If you want to see the where clause your filter created, you can go to your VBA editor and use Ctrl + G to display the Imediate Window.

Dim strWhere As String
strWhere = Me.Filter
MsgBox strWhere
Debug.Print strWhere
DoCmd.OpenReport "rptName", acViewPreview, , strWhere, acHidden
DoCmd.SelectObject acReport, "rptName", False
DoCmd.OutputTo acOutputReport, "rptName", acFormatPDF
DoCmd.Close acReport, "rptName"

Anyway, this is just to get you started. The query you are using seems rather complicated. It seems as if you are going after a mail merge to get the PDF customized to each client. If your current query is doing this let me know. Then it is just a matter of looping through the records in the query, and emailing that current record.

If you filter the form to the current record and use the above code, will that be the PDF you are looking for?


.

[voldatabase]

Thank you VERY much for this. Looks great but only just read through it now. Our IT dept. has not yet confirmed the installation of CDO on our Exchange server, so I will not be able to try CDO based code yet.

I just wanted to ensure you that the SQL code sent in my last reply DOES work very well for printing / 'displaying in print preview' the required report, and also when just running the query. It also allows for the correct filtering prior to sending the result as input criteria for a MS Word Mailmerge (a suggestion to achieve a better (?) result, from some or other forum I have trawled in the search for answers). I guess the SQL is not easy (looks a lot easier in the Query Builder GUI, but still quite complicated) in that we are working one month ahead based on different criteria, to give our clients time to meet our externally imposed deadlines.

I am a bit puzzled as to where I put the 'strWhere' (or how to pass it the relevant variable - OrgID / EmailAddress etc.) in order to fully automate this process of printing individual pages / records to individually named files, since all the filtering of the bulk database has been already done by the SQL query, and what is left is the list of relevant clients to be contacted for the current month. For instance, the report for October'13 has reduced ~150 clients to only four (4) that need to be contacted now.

You mention in your 1st post "make sure you have a folder and a text file on your C drive with the correct name. You need a folder named "Test" and a plain .txt file named "Test" inside that folder, if you use the attachment code." I am not sure what this "plain .txt file named "Test"" file contains.

Is plain .txt file the EMail Client list - viz. export query result to .txt as a lookup table?

Will the code supplied produce the relevant PDF's for attachment to an EMail?

How does the 'email code' pick up the relevant attachments from the 'print_to_pdf_folder'?

You mention in your 2nd post "just a matter of looping through the records in the query, and emailing that current record". Using the currently working query, how do I do this. This is where I get totally stuck, not knowing VBA.

Below are some of the 'pseudo code' ideas I have been toying with in the past. As you can see, this was written before I became aware that 'DoCmd.SendObject' is not adequate for the task, and also involves the use or Word's mail merge feature to create the document, which, if I could get it all done in Access, would not be needed. According to the MS Access Help, 'DoCmd.SendObject' is (apparently) not able to handle graphic objects as are contained in the report / .pdf. If any of the 'pseudo code' ideas, below, are relevant / required please would you be able to help me complete the task, as I bog down BIG TIME at this point!!!

' 1. Let Access / Word do the mail merge using VSM data and then create a .PDF per EmailAddress with the filename of "[EmailAddress]_[systemdate].pdf" in a subdirectory called ..\01 to ..\12, based on the month of the print run. Since each document created will have a unique name based on the "[EmailAddress]_[systemdate].pdf" and that name also contains the year in [systemdateTime], the correct .pdf should be easy to reference in point #2 below.
{Dim filename, recipient, docdir, systemdate as string 'Dimensioning variables
Dim yrstr, systemyearstr as string 'Dimensioning variables
systemdate = Format(Date, "ddmmyyyy") 'Get system date from RTC in format ddmmyyyy
systemyearstr = Right(systemdate, 4) 'Returns string value for the yyyy part of 'format ddmmyyyy'(viz. 2013)
docdir = Mid(systemdate, 3, 2) 'Returns string value for the mm part of 'format ddmmyyyy'(viz. 01 to 12)
filename = [EmailAddress]_[systemdate]
wdApp.ActiveDocument.SaveAs filename as type pdf in docdir 'Save files in .pdf format in the specified directory (viz. 01 to 12)

' 2. Send EMailed attachment to the EMail as read from the first part of the FileName.pdf and ensuring that the correct year is EMailed.
{Dim filename, recipient, docdir as string
Dim yrstr, systemyearstr as string
systemdate = Format(Date, "ddmmyyyy")
systemyearstr = Right(systemdate, 4) 'Returns string value for the yyyy part of 'format ddmmyyyy'(viz. 2013)
docdir = Mid(systemdate, 3, 2) 'Returns string value for the mm part of 'format ddmmyyyy'(viz. 01 to 12)
yrstr = Right(filename, 4) 'Returns "2013" or later
if yrstr not_equal_to systemyearstr in docdir
then find different filename
recipient = Left(filename, [while chrstr not_equal_to "_"])
While yrstr = systemyearstr in docdir
'SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
DoCmd.SendObject(file, docdir/filename.pdf, , "recipient", , , "Insurance details update please", "Attached please find a request for your updated Insurance details.", False, ,)
}
All the above is based on the belief that the date strings returned ARE indeed in the correct format, and not some incomprehensible integer, as I had Excel throw at me the other day. :-(

Once again, many, many thanks for your efforts.

Alex

[ItsMe]

I mention CDO because you do not want to depend on Outlook. You can use the example in post # 7 to explore how CDO works. Post # 7 is only an example. With a server IP, username, and password you can test the code. You only need to create a temp folder and text file to use the EXAMPLE. The example will demonstrate the ability to grab a file on your hard drive and attach it to the email. The example does not require any special software or Outlook to be installed on the client. You could use a Gmail account to test it. If you can set up email on your phone, you have the info needed to test the example in post # 7.

The downfall to CDO is I don't know its full capabilities of formatting. It may prove difficult or impossible to format a banner with a jpg in your email body for example.

Using the Docmd could work if you base it off of the appropriate query. This is why I asked about the SQL. It seems like this is doing a mail merge. If this is the case. It will make the DoCmd a little more difficult. Also, I believe using the DoCmd requires Outlook to be installed on the client.

I think what you should be asking yourself is which way would you like to pursue. That is why I offered post # 7. It can supply you with some answers quickly. Try it out and see if it seems like something you can work with. If not, then you need to see how you can deal with using Outlook.

Let me know. We can explore both. I just need you to try some things and make some choices after you discover what may or may not work for you.

[voldatabase]

I would much rather bypass any need for Outlook or any other application. Pure Access would suit me best as the query already does what I need (to print) without any outside (Word) requirements.

If we can just get the report / query to save the individual pages of the current Access 2010 report as individual .pdfs, which can then be picked up using some VBA magic and 'printed' / sent to a SMTP server matching the relevant EMail address per .pdf file - WONDERFUL!!!

The pseudo code I supplied was merely ideas I was kicking around previously and is not in any way to detract from our current (CDO) path. I thought that the .pdf file naming convention may help the coding / file matching with the client EMail Address.

I will have a look at #7 on Thursday and hopefully our IT dept has installed CDO in the Exchange Server by then.

Thanks

Alex

[ItsMe]

The pseudo code I supplied was merely ideas I was kicking around previously and is not in any way to detract from our current (CDO) path. I thought that the .pdf file naming convention may help the coding / file matching with the client EMail Address.


Thanks

Alex

That is how it was received. I have not scrutinized it yet, but I will. This way we can stay on the same page. Let me know what you think of post # 7. It is the only way I know of to avoid using an active account within Outlook client.

[voldatabase]

Thanks for that.

I tried testing the code in #7 and got some different results depending on whether the recipient was on the 'internal domain' address or on an external (hotmail.com) address (mine), as part of a ‘real world’ test, since we need to email these reports to our clients, externally.

I created a .pdf in a local (d:\temp) directory
Started a new Access Database session
Created a simple form with a toggle button on the form
Made the various substitutions in the code (in an external text editor) - recipient address, file locations, server IP address etc.
Attached the now modified code to the toggle button 'Click Event'
Saved it
Went back to the form and clicked on the toggle button
I tried sending to the external mail domain with both "...smtpauthenticate") = 1" and "...smtpauthenticate") = 0" # anonymous sender, while commenting out the usernames and password, as I am not too happy about hard coding usernames and passwords in a 'clear text' environment.
I also tried sending to 'internal (local mail domain) addresses, but this time I used "...smtpauthenticate") = 1" with the attendant changes to the usernames and passwords

The ‘internal’ emails gave no indication whether they went or not, so I assume that they went. Unfortunately both recipients were out of office today, so I have no way of knowing if these tests have worked, but have EMailed them to let me know if the tests arrived.

Going to the ‘external’ address I got the following error message:-

Error message begins <

Run-time error ‘-2147220977 (8004020f)’:

The server rejected one or more recipient addresses. The server
response was: 550 5.7.1 Unable to relay

> Error message ends

So far, my research seems to indicate that there may be an issue with the server configuration, to allow for emails to be sent to external addresses.

I have emailed our IT dept. with this, and asked them to check and fix.

Unfortunately, with the further deterioration in our local economy, I am only working on a Thursday now (even volunteers get their work time cut), so I only have one day per week to implement the code and test it, so this will be a very slow process.

BTW. You mentioned in #7 "blank acddb and also with a blank MDB".

I think I know what a blank MDB is (new Microsoft Access Database), but what is a "blank acddb"?

Thanks and regards

Alex

[ItsMe]

If the SMTP server requires authentication and you do not provide a username and a password, the SMTP server will not relay and error 550.

You can use whatever SMTP server you want. It does not have to be your personal one or one that manages sensitive business transactions.

In my example I use this
sendemailaddress") = "Do Not Reply <Automated@MyDomain.com>"
You could probably get away with having it equal "Have a nice day"

This is what the recipient sees when they open the email. It is what is used when they click the reply button.

.accdb is the file extension for 2007 and newer Access databases. Previous versions are .mdb

[voldatabase]

Thanks again for that Info.

I have finally ascertained is that NONE of the test emails have gone through using this code - internal or external target email addresses, despite there being no indication that the internal EMails had NOT gone through.

I have tested again sending to the external EMail (my hotmail account) using my own Outlook user name and login hard coded into the CDO schema lines:-

.../sendusername") = "MyUserName@Our.Mail.Domain"
.../sendpassword") = "***mypassword***"
.../sendemailaddress") = "Do Not Reply <MyUserName@Our.Mail.Domain>"

I still get the same error message (see below).

Error message begins <
Run-time error ‘-2147220977 (8004020f)’:

The server rejected one or more recipient addresses. The server
response was: 550 5.7.1 Unable to relay
> Error message ends

I also tried editing the information in the .../sendemailaddress based on information / 'Exmaple' found on MS web site on the subject at 'http://msdn.microsoft.com/en-us/library/ms526584%28v=exchg.10%29.aspx', as follows:-

.../sendemailaddress") = "Do Not Reply MyUserName@Our.Mail.Domain"
.../sendemailaddress") = "MyUserName@Our.Mail.Domain"
.../sendemailaddress") = "<MyUserName@Our.Mail.Domain>"
.../sendemailaddress") = <MyUserName@Our.Mail.Domain>
.../sendemailaddress") = "Do Not Reply" <MyUserName@Our.Mail.Domain>
.../sendemailaddress") = "Do Not Reply" MyUserName@Our.Mail.Domain

The last three edits do not even allow me to move my cursor out of the edited line - I get an error <Compile error: Expected: end of statement>.

The last line, which matches the example ("Some One" example@example.com) at the above mentioned MS internally hosted support site (DN - Developer Network{?}), seems to indicate that their support website is wrong, or that they have not updated this information for Access 2010. I have searched both inside MS and using Google for information pertaining to Access 2010 regarding this coding and found it interesting to note that MSDN seems to be in the minority to carry that coding example.

When I click the <Debug> button, the break is always at:-
.Send

I even tried moving the .../smtpserverport") = 25 to 465 as per http://www.rondebruin.nl/win/s1/cdo.htm in the hope that this would fix the problem. No such luck:-
Error message begins <
Run-time error '-2147220973 (80040213)’:
The transport failed to connect to the server.
> Error message ends # wrong port!!!

I then looked for a port scanner to check for the SMTP port ('netwox' equivalent) and ran the MS supplied portqueryui.exe to scan the listening TCP and UDP ports on the Exchange Server, and 25 is it, as you correctly surmised.

I also reversed the order of the lines:-
.../smtpserver") = "xxx.xxx.xxx.11" # IP address
.../smtpserverport") = 25 # TCP / UDP Port for SMTP
This was done to make it more like one would use in a browser xxx.xxx.xxx.xxx:9999 # IP_address:PortNo

Still no luck - same error message "rejected".

I then changed the:-
.../smtpserver") = "xxx.xxx.xxx.11" # IP address
.../smtpserver") = "InternalServerName" # Internal Server Name as supplied by nslookup

Same Story.

At this stage I STILL have not got feedback from our IT department as to whether CDO has been implemented on the Exchange server yet, but have now gone "one up" to try and get some activity on this front.

Thanks again

Alex