Hyperlinks to send email to multiple recipients with multiple CC

Copper Contributor

Hello Everyone,

 

Thank you for your support in advance

 

My query is just to create a hyperlink to be able to send an email to multiple recipients and multiple CC, So I have come so far with this
"=HYPERLINK("mailto:"&$D$6&";"&$D$7&";"&$D$8&";"&$D$9&";"&$D$10&";"&$D$11&"?subject="&$A$2&"&cc="&D12&";"&D13&";"&D14&";"&D15,"Send")"

 

However, once I type 2nd CC it shows me #value! and I want to add multiple CCs but no errors show in front of me if I type one CC which means if I stop at "D12 "so any ideas on how to solve this issue.

 

15 Replies

@EzzEldean 

Don't be so impatient! Forum members are users like you who help others as volunteers. Expecting a reply within 20 minutes is not reasonable.

 

Your formula works OK for me. Make sure that the cells that you refer to contain valid email addresses.

 

S1494.png

Appreciate your answer.
it's just a task that we need to do that's why we are in a hurry as we have a deadline regardless if you could help me with this matter as it still shows me #value! even after checking.

@EzzEldean 

Here is a demo workbook with the formula in cell A1.

What do you see?

@Hans Vogelaar 

ِOf course appreciate your efforts but I really don't know why it keeps showing me the error

 

any ideas or even another way to do the same purpose.

EzzEldean_0-1654640458377.png

 

@EzzEldean 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

German formulas need ";"
English formulas need ","

@Hans Vogelaar 

 

my apologies but it shows me not supported 

but all I want to do is just be able to press the send button to send to multiple people and cc Multiple

people

is there even a VBA for this istead?

thank you for your reply but if I do this it won't appear in outlook as emails but as texts which force me to edit them one by one so ; helped me with that

@EzzEldean

Did you find a solution? I have the same issue, I cannot figure it out how to solve it... :( 

First of all my apologies for my late reply.

Yes, I am using a macro now

Sub SendEmail_Name of the Macro()
Dim EmailApp As Outlook.Application
Dim Source As String
Set EmailApp = New Outlook.Application
Dim emailitem As Outlook.MailItem
Set emailitem = EmailApp.CreateItem(olMailItem)
emailitem.To = Range("write down the cell").Value
emailitem.CC = Range("write down the cell").Value
emailitem.Subject = "write down the Subject you want"
emailitem.Body = "Dear Team," & vbNewLine & vbNewLine & "Appreciate your support with the below"
emailitem.Display
End Sub

However, I am struggling with an issue, You can't write down multiple cells as if you want to include the values of multiple cells in the range section, For Example, "Range("A1:B1")", If you type this range the Macro will not work when you run it only works with just one cell like this " Range("A1") ", However, The one who can help you the most is @Hans Vogelaar
Hello @EzzEldean

I think the reason is maximum number of characters, in total - calculation all the cells in formula. I have tested and hyperlink is "crashing" on 210 characters and it is working with 209. I googled this and I found some info that 255 characters is maximum, but mine hyperlink was crashing at 210...

I do not know to use macro, but thanks for your reply anyway, it is a sign for me to learn it :)

We are using a 'form' to update a Spreadsheet 

Is it possible that when asked in the form do you want confirmation email - [tick yes]

For excel to automatically send and email response to the spreadsheet update.

 

We are using the formula 

 =HYPERLINK("MailTo:"&BH2&"?Subject="&BI2&"&cc="&$BA$2&"&body="&BJ2,"EMAIL")

to manually do this

But we want - when cell  [BD2] is "yes"

outlook will auto send confirmation email.