Excel File Hyperlink To Email

[Seeka]

Hi All, Just trying to send Excel file as email by using a Hyperlink to Gmail. The Hyperlink opens Gmail and puts in Heading etc no problem but how to get Excel file into body of email or at least attached. I want it to happen hopefully without input from operator to attach file. Thanks

 

[Mike Lewis]

How exactly are you doing this? Are you actually inserting the hyperlink into the workbook? And, if so, are you choosing "Email link" in the "Link to:" box? If so, then you are out of luck. What you are doing is generating a mailto: link, and there is no way to add an attachment to a mailto: link.

Instead, go to File -> Save & Send -> Send Using Email -> Send as Attachment. That will open a message composing window in your default email client, with the workbook already attached; but you will have to fill in the To:, CC:, Subject:, etc. as well as the body of the message.

This works for me using a desktop email client. I don't know how it works with Gmail, but it would be easy for you to try.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Seeka]

Thanks Mike, I was hoping to be able to do it with a one click solution for my users but if no other solution will try that.
Thanks again

 

[Mike Lewis]

Are you comfortable with writing VBA macros in Excel? And do you have Microsoft Outlook installed?

If so, you could write a macro that creates the message via Outlook, then adds the attachment, then either opens the message in Outlook for the user to preview, or just sends it straight away. The code would be fairly simple. I have done this sort of thing myself on several occasions. I can't give you any actual code, because I am not a VBA expert; I use Visual FoxPro, but the basic concept of the code would be the same.

You could then place a command button in your worksheet, and execute the VBA code from the Click of the button. Or, you can assign a keyboard shortcut to the macro. Either way, the user would then have an easy way of sending the email.

The email would normally be sent from whichever account you have set up as the default in Outlook. If you have a special reason for sending it from GMail, then you should set the mail item's SentOnBehalfOfName property to the GMail address you want the message to come from.

The only possible difficulty I can foresee is if the workbook is sending itself, that is, if the workbook that contains the macro is the same workbook that you are attaching to the message. In that case, it should be enough for the macro to save the workbook before sending it, but I'm not completely sure about that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

I don't know if this will be any help, but here is some bare-bones code. The code is iVisual FoxPro rather than VBA, but, as I mentioned above, the concept is identical. You would only need to adjust the syntax for VBA. If nothing else, it should give you the general idea.

oOut = CREATEOBJECT("outlook.application")
oMail = oOut.CreateItem(0)  && creates the mail message
oMail.subject = "Subject goes here"
oMail.to = "xyz@example.com"   && recipient's address
oMail.SentOnBehalfOfName = "myaddress@gmail.com"  && GMail address to send from
oMail.Body = "Text of your message goes here."
oMail.Attachments.Add("c:\work\mMyWorkbook.xlsx")  && this is the attachment
oMail.Display	&& opens message in Outlook for preview and editing
* Or you can do oMail.Save to save it in the Drafts folder,
* or oMail.Send	to send it straight away (doesn't need Outlook to be open)

If you prefer to see some fully-working VBA code, I feel sure that one of the experts here will be able to provide it. Or you could try searching on-line. This is a fairly common requirement, and has no doubt been done many times before.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads