Emailing attachments macro

I am stumped here. I am not to familair for VB code, and am trying write one to search a range of cells and attach all files located in those cells to an email. I can get most of the code to work, but am having difficult getting it to search through specific cells. Below is my code: can anyone help?

Please Login or Register  to view this content.

Hello Jat82nd,

Can you show the layout of the cells used in your email? It is not clear to me what you have where. Once I see where things are, I can amend your code.

Sincerely,
Leith Ross

Thanks... it is microsoft outlook. Here is a screen shot, just in case you need one.

Hello Jat82nd,

Try copying/posting the jpg again. It comes up blank.

Thanks,
Leith Ross

I attached a .jpg

Hello Jat82nd,

Close, the window frame is there, but still no joy on the picture.

Sincerely,
Leith Ross

Maybe we should try it this way... :-) This code works great, but I can't figure out how to add in the get file from cell code.

Please Login or Register  to view this content.

Hello Jat82nd,

It looks like your are triggering the macro by clicking on object located on the worksheet. Is the email cell selected by the user before the object is clicked to run the macro, or is the object located next to the email address?

Sincerely,
Leith Ross

The button is just below the cells that I want to pull the files from. The button in cell H31, and the cells I want to put the files from are H26:H30. Random files will show up in those cells. Sometimes there may only be one, and other times all the cells my be field. I am not sure if it will matter, but the files that showup in those cells are inputed by a formula and are hyperlinked to the actual files.

Hello Jat82nd,

This macro will add all attachments in the Range("H26:H30") if there are any.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hello Jat82nd,

Disregard my previous post. That macro won't work for you because you creating the Hyperlinks using the =HYPERLINK worksheet function. The hyperlinks created in this manner aren't seen by VBA. Use this macro code.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

I actually had the same problem with both. Neither would attach the files, but based off from you comment as to why, maybe it is becuase of the formula I am using. Below is the formula in each cell:

HTML Code: 

=IF(VLOOKUP('data drop down'!D3,'Specs file links'!A:B,2,FALSE)="","",HYPERLINK(CONCATENATE(VLOOKUP('data drop down'!D3,'Specs file links'!A:B,2,FALSE))))

Hello Jat82nd,

You have a couple of options. You can use VBA to create and insert the Hyperlinks, or you can set the cells value to the file's path. The method you are using is not an option since there is no way to make it work. Some might suggest checking the text font. The way the text appears for the link, and the way the cell is formatted are not the same. The cell format is regular font (no underline), and the color index = 5 (blue). This remains the same even after you click the link and it changes to that lavender like color.

Sincerely,
Leith Ross

Can it be down similar to that of what happens when you just want to attach a file normally? Basically what is displayed in those feilds looks like this:

C:\Documents and Settings\jtewell\Desktop\marketing\infinity.pdf

If I have to have VB assign a hyperlink to it, than that is fine. I just couldn't figure out how to do it that way. So I just used the formula I figured out to bring the hyperlink over. I figure VB could still read the hyperlink, because it is listed as the file path, but what you are saying.. if I understand correctly, is that VB cannot read it, because it has a hyperlink attached to it.

If I can do both with a VB code, then that would be the easiest and best for me.

Hello Jat82nd,

If you can get the cells to display the file path i.e., C:\Documents and Settings\jtewell\Desktop\marketing\infinity.pdf, that will work. The Outlook Attachment collections requires the full file path, which is what you have here. This would be the quickest and easiest solution to implement.

Sincerely,
Leith Ross

I have actually tried it three different ways. All three ways though in the cell the file name is referenced just like how you have it below. So I have one cell that is just the reference name, no formula's or hyperlinks... just the file name and location. Then I have one that uses the formula I mentioned a few post ago, and the last one uses an =reference cell, with no hyperlink, just the file name.

In all three cases though the cell is displays the file name and location, and none of them will attach themselves to the email.

So I have been playing around with your code a bit, and this is what I am seeing. When I use the F8 command it will go through the entire code, but when it gets to:

Please Login or Register  to view this content.

it skips the rest. If I switch out the "=HYPERLINK" to "=IF" and change the 10 to a 3, it will go through the rest of the code, but will still not attach any of the files.

I am assuming that the next problem is in the line:

Please Login or Register  to view this content.

But I am unable to figure out what each part of the code means. I think the Chr$(10) means the number of characters in the row, but even increasing that, doesn't help. The rest I am not sure of.

Any thoughts, or can you explain to me what that line is saying?

Okay... getting a bit closer. By removing:

Please Login or Register  to view this content.

statment from above the

Please Login or Register  to view this content.

statement, when it opens the email thread it comes up with the attachment box, but still not attaching any files to it.

I got it! This is the code that worked for me:

Please Login or Register  to view this content.