VBA to send email / Paste from Excel into Email Body / Paste subject line from excel / paste email from excel / attached file from directory
- Thread starter Jenn1981
- Start date
BobBridges
Active Member
Jenn, I'd never seen that link before; looks like it's full of good information that would have been a welcome help to me when I was first figuring out how to do Outlook stuff from Excel. Go ahead and work with that—but if parts of it still confuse you, come back to this thread and ask.
Hi Bob,
Its very confusing. Would you have an excel file already has all the items I had listed. This I can work on it backwards and change the coding. Thank you for your time , Jenn 
BobBridges
Active Member
I'm hesitating. I more a teach-a-man-to-fish kind of guy; I don't want to just give you a working program, I want to explain how to do it. That way when you do it yourself (with help from me), you'll know what you did and can modify the program yourself.
But, what the heck, I can at least show you some statements I use:
This, I expect, will leave you with an email that yo can type text into and send. If you want the program to include the text, too — if the body of the email is going to be the same every time — there's more than one way to do it, but we should talk about it first.
But, what the heck, I can at least show you some statements I use:
Code:
' First, connect to the Outlook application object. This is like the Excel application object, but it's for Outlook instead of Excel. After
' this, ool contains all the other stuff you need to interact with Outlook.
Set ool = GetObject(, "Outlook.Application")
' Now start a new email:
Set oeml = ool.CreateItem(0) '0 indicates an email rather than one of the other obects Outlook can deal with, like a task or a
'calendar appointment. From now on we'll be using the oeml object.
' If your Outlook client has more than one email account — maybe you fetch your business email there as well as personal — you may need to pick
' the account you want to use. If you have just the one account, you don't need this statement.
Set oeml.SendUsingAccount = ool.Session.Accounts.Item("my.email@gmail.com")
' Set some of the email's fields:
oeml.To = <AddrTo> 'or more than one address
oeml.cC = <AddrCc>
oeml.Subject = <Subj text>
' I'm not real confident about Outlook programming, but I think the Inspector is a part of Outlook that displays an email. This statement is
' needed either to make the email visible on your screen, or maybe just to bring it into the foreground on top of the Excel workbook you
' were working with. You should experiment with leaving out this statement to see what happens.
oeml.GetInspector.Activate
' I gather you want to add an attachment. The program I'm copying from doesn't do that, but I'm pretty sure it works like this (for example):
oeml.attachments.add "C:\path\filename.txt"