Attach excel file according to path and email vba

[rakesh seebaruth]

Hello Guys

I have the following link

testing.xlsm

Shared with Dropbox

www.dropbox.com

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

 

[daverunt]

Sorry wrong post ....

 

[daverunt]

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.

'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

 

[rakesh seebaruth]

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

 

[daverunt]

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

 

[rakesh seebaruth]

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