How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask NewITZone Your Own Question
NewITZone
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
53509759
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

Im trying to send a note from an EXCEL (Save&Send) macro and

Customer Question

I'm trying to send a note from an EXCEL (Save&Send) macro and SAVE the file first (which I have done) and attach a file in VBA "without" using anything associated with Outlook (I'm using: ActiveWorkbook.FollowHyperlink). GMAIL is used by the person who will be using this macro and I use OUTLOOK but I need the solution to work in both environments. A solely Outlook solution will NOT solve my problem.

I can build the note (using the following code) but I have two problems:

1. The note opens but is NOT automatically sent.
2. I am UNABLE to include an attachment (i.e., the actual XLSM file from which I pressed the "Save&Send" macro).

Can this be automated?

I have define "Recipient", "Subj", "MyFileName" and "Msg" in earlier code.

HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "file=" & ThisWorkbook.Path & "\" & MyFileName & "&"
HLink = HLink & "body=" & Msg & "&"

ActiveWorkbook.FollowHyperlink (HLink)

Thanks,
Roger
Submitted: 5 years ago.
Category: Microsoft Office
Expert:  Rachel-Mod replied 5 years ago.

Hi, I’m a moderator for this topic and I wonder whether you’re still waiting for an answer. If you are, please let me know and I will do my best to find a Professional to assist you right away. If not, feel free to let me know and I will cancel this question for you. Thank you!

Customer: replied 5 years ago.
Thank you Rachel, I am still waiting for a response. Thanks, Roger
Expert:  Rachel-Mod replied 5 years ago.

Sometimes, finding the right Professional can take a little longer than expected and we thank you greatly for your understanding. We’ll be in touch again shortly

Customer: replied 5 years ago.
Okay Rachel, with the holiday weekend upon us (Easter and Passover), I'm sure it will be difficult to find someone. I can wait until next week. Just so you are aware, the $22 was already charged to my account. I thought it would have been charged only if I got a satisfactory response. Roger ([email protected]).
Customer: replied 5 years ago.
Here are two options I am trying with comments on how successful they are.
'********************************************************************
' This code sends the note automatically & includes a body but does NOT include the attachment. *
'********************************************************************
MyFileName = "Master File " & MyFileName & "xlsm"
Recipient = "[email protected]"
Subj = "Master File"
Msg = "Attached please find the latest Master File." & _
"%0A%0A" & ""

HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "file=" & ThisWorkbook.Path & "\" & MyFileName & "&"
HLink = HLink & "body=" & Msg & "&"
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%s", True
'********************************************************************
' This code does NOT send the note automatically or include the body but DOES add an attachment. *
'*********************************************************************
Application.Dialogs(xlDialogSendMail).Show _
arg1:="[email protected]", _
arg2:="Latest Master File"
Application.Wait (Now + TimeValue("0:00:01"))
' The following line does not appear to work here but up above it does work. Don't know why.
Application.SendKeys "%s", True
Expert:  Rachel-Mod replied 5 years ago.

Thank you for your patience, your business is very important to us, we are waiting on the professional with the right expertise to come online. Feel free to let us know if you would like us to continue searching for a professional or if you would like us to close your question. Thank you for your understanding!

Customer: replied 5 years ago.
Rachel, any word on someone reviewing my question? I've already been charged and have received no input. Please advise.
Expert:  Lindie-mod replied 5 years ago.
Please understand that it’s rare when we cannot find the right Professional Nevertheless, because you are entitled to your money back with no questions asked, we will process your refund immediately, upon your request. Please accept our sincere apology. We hope you’ll think of us again when the need arises

Related Microsoft Office Questions