SENDING AUTO EMAILS WITH ATTACHMENTS TO VARIOUS RECIPIENTS

[fusion786]

Hi

I would like some help please with setting up a way of being able to email files (attachments) to various recipients at the same time automatically.

All the recipients will get different files so I will need to assign a folder that will hold the files for each recipient to a email address.

Each folder will contain 3 files and there will over 200 folders in total ie over 200 emails will need to be sent.

Any help offered would be appreciated.

Thanks

 

[paulbent]

Are you running this server-side? Look into CDONTS (NT 4) or CDO for Exchange 2000 (W2000).

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[fusion786]

Hi Paul

No I am running this from a pc using windows 2000.

Basically to explain the process, in total I have about 200 x 3 reports that are currently printed and sent out to the various recipients on a monthly basis.

The idea is to cut out the flow of paperwork.

What I am proposing to do is set up the required number of folders ie about 200 in a specified directory where the reports would be saved into on a monthly basis.
Then when the reports are ready to be sent, I want to be able to email the reports out automatically to all the recipients without having to send each one manually.

I have kindly been given some coding that was used in excel but which I am told will work fine in word but I do not how to how to link the folders to the email addresses and build that in to the code.

This is the coding that I have:

Function SendMailByOutlook() As Boolean

On Error GoTo Err_SendMailByOutlook

Dim objOutlook As Object
Dim objMailItem As Object

SendMailByOutlook = False

Set objOutlook = CreateObject("Outlook.Application&quot
Set objMailItem = objOutlook.CreateItem(0)

With objMailItem
.To = Worksheets("Lists&quot.Range("R4&quot
.Subject = "Customer Name - " & ActiveWorkbook.Worksheets("InputData&quot.Range("M2&quot.Value
.Attachments.Add ActiveWorkbook.FullName
.Display
End With

Set objMailItem = Nothing

SendMailByOutlook = True

Exit_SendMailByOutlook:
Exit Function

Err_SendMailByOutlook:
MsgBox Err & ": " & Err.Description
SendMailByOutlook = False
Resume Exit_SendMailByOutlook

End Function


All help is appreciated. Thanks.

 

[paulbent]

OK, you should be able to do this with the Outlook object model.

Where do you have the e-mail addresses, in Outlook Contacts? How about naming the folders with the contact's first and last names separated by a space. Make them all subfolders of a Reports folder.

You can use the FileSystemObject (set a project reference to Microsoft Scripting Runtime) to get a reference to the Reports folder and loop through its subfolders collection. For each subfolder, parse the first and last names and use the Find method of contact items to return the contact.

Here's a rough idea, I'm just typing this in so please excuse any errors

Dim objApp As Outlook.Application
Dim objNS As Namespace
Dim objFSO As FileSystemObject
Dim objRoot As Folder
Dim objFolder As Folder
Dim objFile As File
Dim objContFldr As MAPIFolder
Dim objContact As ContactItem
Dim objMsg As MailItem
Dim objRecip As Recipient
Dim strFirstName As String
Dim strLastName As String
Dim strCond As String

'Get the contacts folder
Set objApp = New Outlook.Application
Set objNS = objApp.GetNamespace("MAPI&quot
Set objContacts = objNS.GetDefaultFolder(olFolderContacts)

Set objFSO = New FileSystemObject
Set objRoot = objFSO.GetFolder("X:\Reports&quot
For Each objFolder In objRoot.SubFolders
'Move thro the subfolders, parse the name
strFirstName = Left$(objFolder.Name, _
Instr(1, objFolder.Name, " &quot - 1)
strLastName = Right$(objFolder.Name, _
Len(objFolder.Name) - Instr(1, objFolder.Name, " &quot)
'Return the matching contact
Set objContact = Nothing
strCond = "[FirstName] = """ & strFirstName & """ _
and [LastName] = """ & strLastName & """"
Set objContact = objContFldr.Items.Find(strCond)
If Not objContact Is Nothing
'Create the e-mail
Set objMsg = objApp.CreateItem(olMailItem)
With olMailItem
'Add the recipient
Set objRecip = .Recipients.Add(objContact.Email1Address)
objRecip.Type = olTo
objRecip.Resolve
.Subject = "Monthly reports"
.Body = "blah blah blah"
'Add a couple of blank lines before adding attachments
.Body = .Body & vbCrLf & vbCrLf
'Add the attachments
For Each objFile in objFolder.Files
.Attachments.Add objFile.Name
Next
.Send
End With
End If
Next

'Set all object refs to Nothing

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[fusion786]

Hi Paul

Thanks for that. The email addresses are stored on a global contact list (the pcs are networked) but if necessary the required contacts can be set up on the pc that would be used for the reports.

The reason why I may not be able to name the sub folders as first and last names is becuase before I will get to this stage of emailing the reports out I need to carry out a few steps before.

I have set up a thread for some advice on this, the thread is thread68-667786 (MACRO/VBA IN WORD) in the office forum but let me explain a little so that you can get an overall picture of what I need to do.

Firstly, I have one big word file which could contain over 200 reports within the file. I need to be able to split the file into the various reports automatically and then each report to also save automatically.
Each report has the same header and the identifier to say that it is a new report is centre 1234, centre 1235, etc so I wanted to save the report using the centre number eg 1234 as the filename in a specified folder.
I have kindy been given a coding that would help (I have listed the coding in the other thread)but this coding splits the report at each page and I'm not sure how to edit the code so that it does a search and splits the report where it picks up a change in centre and also how to save the file using the centre number as the filename.
Also the split reports layout format changes from the master file.

I will have 3 different files would be emailed to each recipient. The other 2 files will be in .pdf format rather than word.

Once I can achieve this then I am ready to email.

If you know a way to split these reports as required then it would be most welcome.

Here is the coding as it stands:
Sub splitter()
'
' splitter Macro
' Macro created 16-08-98 by Doug Robbins to save each page of a document
' as a separate file with the name Page#.DOC
'
Selection.HomeKey Unit:=wdStory
Pages = ActiveDocument.BuiltInDocumentProperties(wdPropertyPages)
Counter = 0
While Counter < Pages
Counter = Counter + 1
DocName = &quot;Page&quot; & Format(Counter)
ActiveDocument.Bookmarks(&quot;\Page&quot.Range.Cut
Documents.Add
Selection.Paste
ActiveDocument.SaveAs FileName:=DocName, FileFormat:= _
wdFormatDocument, LockComments:=False, Password:=&quot;&quot;, _
AddToRecentFiles:= True, _
WritePassword:=&quot;&quot;, ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
ActiveWindow.Close
Wend

End Sub


Thanks very much for your advice so far.

 

[paulbent]

You'll probably get better Word support in the Office forum. I use Word for otput quite a lot but it's usually simple stuff like creating a doc from a template and replacing bookmarks with data. I'm no Word expert.

I've done many personalised bulk mailing projects with pdf attachments and it's usually better to generate the pdf, create the e-mail and send it in the same procedure. Saves having to create a structure of pickup folders as you're proposing.

I use the Zeon DocuCom PDF driver for PDF creation:

http://www.zeon.com.tw/

and my PDXPro ActiveX dll to configure the PDF settings at run-time. There are around 140 properties that can be set so you have a lot of control over the pdf output. There are also settings in the DocuCom driver to deliver the pdf by e-mail rather than save it to the file system so you may not need to use Outlook in your code at all.

http://www.northwindit.co.uk/pages/PDXPro.htm

There's a case study on my web site:

http://www.northwindit.co.uk/pages/CaseStudyTCS.htm

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[fusion786]

Hi Paul

Thanks for that.

I am interesting in what you are suggesting.

The other 2 reports that I need to send out with the report that will be as a word document are produced in a report writer software. I am able to save these 2 reports as a .pdf file but I am trying to figure out how to save these reports all in one go under invidual names as at the moment I produce one report for each centre at a time and save individually.

If there is no coding available easily for what I need to do then maybe I can do what you are suggesting to generate the pdf, create the e-mail and send it in the same procedure
but I will still need a way of being able to split the reports in word.

Thanks once again.

 

[dr486]

'Reference ADO 2.5 lib
'Reference CDO for Windows 2000 lib

Function cdomail()

Dim cConfiguration As New CDO.Configuration
Dim cFields As ADODB.Fields
Dim cMessage As New CDO.Message

Set cFields = cConfiguration.Fields
With cFields
.Item(cdoSMTPServer) = &quot;mail.server.com.au&quot;
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Update
End With
Set cMessage.Configuration = cConfiguration
cMessage.From = &quot;email@address.com.au&quot;
cMessage.To = &quot;email@address.com.au&quot;
cMessage.Subject = &quot;Subject&quot;
cMessage.AddAttachment &quot;C:\file.att&quot;
cMessage.Send

End Function

' Hope it helps

 

[fusion786]

Hi dr486

Thanks for that.

The one problem that I have is that not all recipients will be sent the same attachments. All recipients will get different reports so I need a way of linking the reports folder (I was thinking of setting a folder for each recipient that will be sent an email will the three reports that they will receive) and the email address.

Thanks