attaching PDF files via a command button

[adnan_razaq]

Hi,

I have a spreadsheet which I will require to attach and send PDF files.

I need to create some functionality that a command button (or anything functional) will review a value of a particular cell and will attach the corresponding PDF files to an outgoing email.

For example, if a customer as a code of EUK ; they have requested for UK and European charts, therefore based on that I will need to attach the UK and European PDF files to an email and be able to send the charts to the customers email address which will also be in the spreadsheet, for instance in cell C5

Thanks in advance

[saravnepali]

Try macro like below attached to the command button

Please Login or Register  to view this content.

[Pepe Le Mokko]

@saravnepali

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[adnan_razaq]

Hi,

many thanks for the sharing the logic.

However, I have made slight adjustments to the code but it's not allowing me to send email beyond the first group selected in the conditional statements that is Europe only

Sub email()
Dim OutMail As Object
'send email
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
' If SIGNATURE = True Then .display
.To = ThisWorkbook.Sheets("Sheet1").Range("K10") '
.Subject = "Black Monthly Reports"
If ThisWorkbook.Sheets("Sheet1").Range("H12").Value = "E" Then
.Attachments.Add "C:\Users\arazaq\Documents\Black\European Equity Index GBP June 2019.pdf"
ElseIf ThisWorkbook.Sheets("Sheet1").Range("H12").Value = "ENA" Then
.Attachments.Add "C:\Users\arazaq\Documents\Black\European Equity Index GBP June 2019.pdf C:\Users\arazaq\Documents\Black\North American Index GBP June 2019.pdf"
ElseIf ThisWorkbook.Sheets("Sheet1").Range("H12").Value = "ENAP" Then
.Attachments.Add "C:\Users\arazaq\Documents\Black\Continental European Equity Index Fund (UK) Class L ACCU GBP June 2019.pdf C:\Users\arazaq\Documents\Black\iShares North American Equity Index Fund (UK) L ACCU GBP June 2019.pdf C:\Users\arazaq\Documents\Black\iShares\iShares Pacific ex Japan Equity Index GBP June 2019.pdf"
End If
.Display 'or '.Send 'to send the email
End With

End Sub

Many Thanks

[saravnepali]

Hi
.Attachments.Add has to be added to each file to be attached

Try this as per below

Please Login or Register  to view this content.

Or try the select case statement if you have many possible options to choose

Please Login or Register  to view this content.

[adnan_razaq]

Hi,

many thanks for your help, it's much appreciated.

however, I have tried the code above above, but when I change the value in cell H12 from 'E' to say 'ENA' then when i run the above macro there's no repsonse

Many Thanks

Adnan

[Pepe Le Mokko]

@adnan

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[saravnepali]

Hi,

many thanks for your help, it's much appreciated.

however, I have tried the code above above, but when I change the value in cell H12 from 'E' to say 'ENA' then when i run the above macro there's no repsonse

Many Thanks

Adnan

Did you mean that it works when the cell value in H12 is 'E' and doesn't work when the value is 'ENA'

[saravnepali]

Hi,

many thanks for your help, it's much appreciated.

however, I have tried the code above above, but when I change the value in cell H12 from 'E' to say 'ENA' then when i run the above macro there's no repsonse

Adna,
Did you meant that code works fine if H12 value is 'E' and doesn't work when its value is 'ENA' ?

That sounds strange.

Is it possible to send your code or even attach the spreadsheet ?

[adnan_razaq]

Hi Sarav,

That's correct.

When I change the value (it is a drop list field), from 'E' to say 'ENA' then it all falls over.

please see the code below

Please Login or Register  to view this content.

Thank you


Adnan

[Pepe Le Mokko]

@saravnepali
This is the third warning you received in a fortnight.
Please take note that any new infraction will be followed by temporary banning.
Thanks

[saravnepali]

See my new code as above

You have still repeated the same mistake

You need to have the .attachments.add for each file you want to attach

For "ENA" case above, you put to files for one line

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

This should work

[adnan_razaq]

Many Thanks for your assistance - all sorted !