Automating sending files from e-mail?

  HKP 12:00 15 Jun 05
Locked

Does anyone know of a way to send a different file to a different person in a list?

I have a list of upto 200 people where each has a unique e-mail address. For each person, I have a file that is called the first 5 charactors of their e-mail in excel. I need to send each person the correct file on a regular basis and want to do this as simply as possible. I use Outlook but am open to suggestion for a solution.

  pauldonovan 12:36 15 Jun 05

You can use it to send emails via outlook. If you put the following code into VBA in an Excel Spreadsheet, and run it, it will begin an email. The .Display causes the email to pop up so you can send it manually. If you replace it with .send it will send it.

If you want me to email you an example spreadsheet let me know, or if you can send me your example I can tailor it to do this.

  pauldonovan 12:36 15 Jun 05

Sub SendWithAtt()

Dim olApp As Object
Dim olMail As Object
Dim CurrFile As String

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)

ActiveWorkbook.Save

CurrFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

With olMail
.To = "[email protected]"
.CC = "[email protected]"
.Subject = "These two files"
.Body = ActiveSheet.Range("D4").Text & vbCrLf
.Attachments.Add CurrFile
.Attachments.Add "c:\My Documents\book.doc"
.Display '.Send
End With

Set olMail = Nothing
Set olApp = Nothing

End Sub

  HKP 13:10 15 Jun 05

Hi Paul,

This is along the lines that I was looking for.

Do you know if this can be done after the event?

I tend to run a routine to generate the 200 files and then I have to check them to make sure they are correct before sending them out. I therefore have a folder containing files like:

P1111.xls
S1935.xls
M3223.xls
.
.
.
etc,

I then need to send the files as follow;
P1111.xls to [email protected],
S1935.xls to [email protected],
M3223.xls to [email protected],
.
.
.
etc,

Would it help if the list of mail addresses was a text file?

Any thoughts?

  pauldonovan 14:48 15 Jun 05

..the code doesn't create the attachment, it expects them to be there.

Are you saying all those files are open in excel? Or saved on the file system somewhere?

Rather than a text file, if you have a list in Excel, you can work from that...I just need to understand how thos efiles are getting created - is that already done and do you have the list in excel ?

  HKP 15:19 15 Jun 05

Hi Paul,

All the files are saved in a folder e.g. C:\2005\Wave4
So all files will be created and I can easily get the list in an excel format. Do you mean each cell has a unique email address or just the first 5 charactors? Either way can be done quite easily.

  pauldonovan 15:38 15 Jun 05

..so you don't need excel to do anything fancy to create any of the files cos they are already there.

If each cell has the 5 characters, you can then write some vba to go through those cells and construct the email.

Are you comfortable with VBA or do you want an example?

  HKP 16:02 15 Jun 05

Hi Paul,

I've messed around a bit with VBA but if you could give me some example code to try I would appreciate it.

  pauldonovan 19:25 15 Jun 05

..I still don't quite get how you produce the files or how they will be in a list in Excel...

You see you could make the code automatically go through the contents of the folder, pick out the filenames, and email automatically without a list.

Or, if your routine is in Excel, you don't need a separate list. etc. etc.

Let me know how it goes.

  pauldonovan 19:38 15 Jun 05

..to get your email address for the example spreadsheet to go to!

  VoG II 19:40 15 Jun 05

To get this sort of code to run properly in Excel, In the VB Editor Tools and tick Microsoft Outlook Object library.

Are the filenames listed in the Excel sheet? If they are it should be possible to adapt the following code which loops through the e-mail addresses in the selected cells and sends an e-mail to all of them.

Sub Email_It()

Dim objOutlk As Outlook.Application

Dim objOutlkMsg As Outlook.MailItem

Dim objOutlkRecip As Outlook.Recipient

Dim Subject, Msgbody As String

Dim cel As Range, sto As String

Subject = "Your subject line goes here"

Msgbody = "Dear All" & vbCrLf & vbCrLf & "Your message goes here" & vbCrLf & vbCrLf & vbCrLf & "Regards," & vbCrLf & "Your name goes here"

Set objOutlk = CreateObject("Outlook.Application")

Set objOutlkMsg = objOutlk.CreateItem(olMailItem)

With objOutlkMsg

For Each cel In Selection

sto = cel.Value

Set objOutlkRecip = .Recipients.Add(sto)

objOutlkRecip.Type = olTo

Next cel

.Subject = Subject

.Body = Msgbody

.Save

.Send

End With

Set objOutlk = Nothing

End Sub

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…