Need VB Code to Extract email address from File name and email the file

**rizmomin** · 07-29-2012, 11:10 AM

Hi:

I have lots of files every day which needs to be emailed to client.
All these files are in C:\"To be Emailed"\
Currently I manually email each file thru Oulook 2010 and this takes lot of time and mistakes.

The structure of the file name which are in the above location :
Date_ClientName_Clientemailaddress
Example of file name : 07-27-2012_rizmomin_rizmomin@comcast.net
07-27-2012_JohnDoe_Jd@gmail.com

I would like to have a VB Script/Macro which will email all files and move the all the files to new location when done.

The body of the email should just say :

"Please find attached the file
Thank you
Riz Momin"

Please let me know if you have any questions.

Thanks

Riz

**TMS** · 07-29-2012, 11:17 AM

=MID(A1,(FIND("_",A1,FIND("_",A1)+1)+1),LEN(A1)-(FIND("_",A1,FIND("_",A1)+1)+1)+1)

This will extract the email name, assuming the format is consistent.

There are lots of examples of sending emails in Excel ... see Ron de Bruin's web site.

Regards, TMS

**rizmomin** · 07-29-2012, 12:34 PM

Hi TMS:

Not all files which needs to be emailed are excel.
Some files are excel and some pdf.
So I would not be aboe to use the above formula.
Please help...

Thanks a lot in advance.

Riz

**TMS** · 07-29-2012, 01:34 PM

You've posted in an Excel forum ... I therefore assumed your file name/email address was in a cell in an Excel worksheet. I've given you a formula to extract the email address. You could use a similar formula to extract the file name. Then you could have a loop to process each of the pairs.

If that's not what you want to do, please provide some clarification.

Regards, TMS

**rizmomin** · 07-29-2012, 01:49 PM

Hi TMS:

Thank you so much for your response.
But as i said i have pdf files also to be emailed.
Please refer to attached pic that shows that these are the files emailed and as mentioned each file has emailed address which needs to be extracted.
Please see if you can help.

Riz

**rizmomin** · 07-29-2012, 04:35 PM

Hi:

Please help me get VB Script for above task (.vbs)

Thanks a lot

Riz

**TMS** · 07-29-2012, 04:55 PM

I will ask around for you but I think you are looking in the wrong place. You need a VB or VBS forum, not an Excel VBA forum.

Regards, TMS

**abousetta** · 07-29-2012, 06:29 PM

Hi Riz,

As TMShucks stated, this is an excel forum, and therefore any solution would 99.99% be Excel-based. Now what you are asking can theoretically be done using Excel visual basic for application (VBA)... but is really a project not a question. Therefore you will need to find someone to really want to undertake a project for fun. Right now, I'm not taking on any new projects but wanted to give you the basic truth so that you can truly reflect on this and tell us if there is anyway we can help answer a specific question.

Good luck.

abousetta

P.S. There is even a canned response for situations like this:

This is a pretty big request in one chunk. I'm sure you'll get better response if you break down your question into several parts, so that they can be tackled one after the other.

Also, we're happy to help you with your effort, but nobody here will be keen on developing a solution from scratch. This forum is about helping people learn how to use Excel, not a free development service.

Have you started any work on this project yet?
How far did you get?
Where are you stuck?

We can help you develop your own code, but if you want someone to code this for you all the way, you should try and find a commercial developer.

**Kyle123** · 07-30-2012, 05:02 AM

I don't think this is a particularly big request and I think VBA code is similar enough to vbs to offer a solution, FWIW you could use this from Excel also

This will work for your examples, however the _ as a space is a poor choice since this is a legal character in email addresses. How would you distinguish between the end of someone's name and the email address if it has a _ in ?

Please Login or Register  to view this content.

**TMS** · 07-30-2012, 06:02 AM

@Kyle: so that's the VBS? I guess I probably could do that with a few worked examples.

Thanks, TMS

**Kyle123** · 07-30-2012, 06:05 AM

Yup, thanks for the feedback it's very similar to VBA just not strongly typed so all late binding, so you obviously can't use any office function in it, although you can create instances of office and use them just like you would in VBA

**rizmomin** · 07-30-2012, 11:18 AM

Hi Kyle23:

You are great and geneous.
This works very good.
The only thing left is to see if the files get moved to another folder once email task is completed.
Also is it possible that this can be emailed in the background instead of actually sending each file thru Outlook.

Thanks a bunch

Riz

Need VB Code to Extract email address from File name and email the file

LinkBack

Thread Tools

Rate This Thread

Display

Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Re: Need VB Code to Extract email address from File name and email the file

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions