Recipients from separate file

[Cardstang]

Hello,

A few of us are responsible for sending out a daily email. Currently, we do this manually and I'm working toward getting it automated. The recipients are fairly static, but about once a week someone asks to be added/removed/or is no longer with the company.

The following code snippet is used to determine who the email goes to:

Code: Select all

  
With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Email Recipients")
              objOutlookRecip.Type = olTo

I've seen it before, but I'm unable to locate how to set the recipients to be located in a separate file.

I'd like to host the recipients in a file in a shared location and have the code point to it to grab the recipients, rather than having the recipients in the code itself. This way rather than everyone updating their code, all that needs updated is the list.

Any help is greatly appreciated.

Thanks in advance.

[HansV]

You could create a distribution list containing the recipients, and send the e-mail to the distribution list. You'd only have to keep the distribution list up-to-date.

If you're on Exchange, the distribution list could be set up on the Exchange Server so that everyone can use it (but only Exchange admins would be able to edit it).

Another option would be to store the e-mail addresses in a text file in a network folder, and use that. Please post back if you still prefer that option.

[Cardstang]

Hi HansV,

Thanks for replying.

None of us have admin rights, so an Exchange distro list is not possible.

I've seen the solution for the text file on a network server. I've googled around for it and can't find it. That's the solution I prefer (or even if it could pull from a spreadsheet on a network server, that's fine too.)

[HansV]

You could add these lines to the beginning of your procedure, substituting the correct path and filename (you can use a drive letter instead of an UNC path):

Code: Select all

  Dim f As Integer
  Dim strLine As String
  f = FreeFile
  Open "\\server\share\folder\file.txt" For Input As #f

and this at the end:

Code: Select all

 Close #f

The lines you posted could then be changed to

Code: Select all

  With objOutlookMsg
    Do While Not EOF(f)
      Line Input #f, strLine
      Set objOutlookRecip = .Recipients.Add(strLine)
      objOutlookRecip.Type = olTo
    Loop
    '...
  End With

This assumes that your text file has one e-mail address per line (i.e. an Enter after each e-mail address).

[Cardstang]

Thanks HansV.

I got it to work. Much appreciated.