Attach excel file according to path and email vba

rakesh seebaruth

Active Member
Joined
Oct 6, 2011
Messages
303
Hello Guys

I have the following link


Actually when i execute the VBA, it works but the only main issue that i am having is that it does not attached the files as stated in Sheet3.

files to be attached :- C:\Users\rakesh\Documents\d.pdf
C:\Users\rakesh\Documents\ocr.pdf
Can you please amend my VBA as per above

Thanking you

regards

rakesh
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Try again:

Looks like you need to create a range for the attachments to add after the filtering?
Then go through the range from G2 onward to attach the files.

Code:
'Dim the new variables
    Dim AttRng As Range
    Dim Cell As Range




 Cnt = 0
        Txt1 = ""
        Txt2 = ""
        HtmlBody = ""
        NumCols = Rng2.Columns.Count
        Set AttRng = Range(Range("G2"), Range("G" & Rows.Count).End(xlUp))  '<------attachment range
        
        For Each Cell2 In Rng2

~~~~~~~~~~~~~~~~~~

With MItem
            .To = Cell1.Text
            '.CC = ""
            .Subject = "Registration Fees"
            .BodyFormat = 2
            .HtmlBody = HtmlBody & "<br><br>"
            '.Send
            .Display
            
            For Each Cell In AttRng
            .Attachments.Add (Cell.Value) ' <---loop through attachment range
            Next
            
        End With
 
Upvote 0
Hi

Thanks for the above . I have made the required amendments .The pdf files are attached to the email but not to their respectives email

Let me explain :

i have change the email addresses as per below

C:\Users\rakesh\Documents\d.pdf hs@intnet.mu
C:\Users\rakesh\Documents\ocr.pdf shr@intnet.mu
C:\Users\rakesh\Desktop\ERGISTRY\x.pdf rs@intnet.mu
C:\Users\rakesh\Desktop\ERGISTRY\y.pdf rakesh1974@orangez.mu

The file y.pdf is the attachment of email ------>rakesh1974@orangez.mu
The file x.pdf is the attachment of email .................> rs@intnet.mu
The files ocr.pdf is the attachment of email -------> shr@intnet.mu
The file d.pdf is the attachment of email ------> hs@intnet.mu

When i execute VBA, only file d.pdf is attached to its respective email that is hs@intnet.mu

the output for the others are as follows :-

File d.pdf,ocr.pdf,x,pdf and y.pdf are attached to mail rakesh1974@orangez.mu
File d.pdf,ocr.pdf,x,pdf are attached to mail rs@intnet.mu
File d.pdf,ocr.pdf are attached to mail shr@intnet.mu

To conclude all attachments have its own email addresses

Thanks/regards

Rakesh

Stay Safe
 
Upvote 0
Hi,

In your example case the d.pdf and ocr.pdf are for email rakesh197436@intnet.mu, these both attach to the mail for the address shown.

Your new content changes that and adds document paths - all with different e-mail addresses?

Can you create a new testing.xlsm file with the content you added in the second post and explain better:

What is the purpose of the filtering?
What mails you expect to be produced and to whom they are sent.
What is displayed in the mail body. At the moment it appears to be the 'complete table' with the filtering off, but that is difficult to confirm with the limited example
 
Upvote 0
Hi Dave

Sorry for the late reply. I must say that the above was a difficult nut for me to crack. After lot of trial and error. I have been able to solve it by adding If Sheet2.Range("J" & Cell.Row).Value = Cell1.Text Then

thanks anyway for your help

stay safe

thanks/regards

rakesh
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top