#  Other Applications & Softwares  > Outlook Formatting & Functions >  > [SOLVED] Drafting emails on Outlook Automatically From XLS Data. Is it possible?

## lifeisaspreadsheet

Hi Experts,

Im looking for a solution to pick data from an excel sheet and get a draft with a specific text created on my Outlook 2007. Is it possible?

The process requires me to send close to 50 mails daily and is quite cumbersome and prone to errors due to repetitive nature. I need to send some information to specific list (each row has different data, so I would need a separate draft for all). 

The sample data and the format of the email is available in the attached file.

Ill greatly appreciate your assistance. 

Please advise.

----------


## lifeisaspreadsheet

On second thoughts. Is this even possible?

Please advise.

----------


## Muzza68

refer to: 
http://www.rondebruin.nl/cdo.htm & http://support.microsoft.com/?kbid=161833

Muzza

----------


## lifeisaspreadsheet

> refer to: 
> http://www.rondebruin.nl/cdo.htm & http://support.microsoft.com/?kbid=161833
> 
> Muzza



Thanks Muzza. This is pretty cool..

But my scenario is a bit different...I need to use my email account to send it, and so was looking if my Outlook 2007 had such a functionality.

Please advise...

----------


## lifeisaspreadsheet

Looks like this isn't possible. Please advise your thoughts...

----------


## lifeisaspreadsheet

> refer to: 
> http://www.rondebruin.nl/cdo.htm & http://support.microsoft.com/?kbid=161833
> 
> Muzza



Good day Muzza,

Hope you are doing great!

Any chance you could help me further on this request?

Please advise.

Thanks!

----------


## Muzza68

I am in the process of implementing my own direct email, but still getting 550 errors from our server.

If you have any success yourself, please let me know......

I would suggest the best way forward for you is to initiate the email from a macro within the excel workbook.
Automation from within Outlook is something I know nothing about.

Muzza.

----------


## lifeisaspreadsheet

> I am in the process of implementing my own direct email, but still getting 550 errors from our server.
> 
> If you have any success yourself, please let me know......
> 
> I would suggest the best way forward for you is to initiate the email from a macro within the excel workbook.
> Automation from within Outlook is something I know nothing about.
> 
> Muzza.



Hmmm...Thanks will let you know if someone is able to help me out.

Best regards,
Life

----------


## lifeisaspreadsheet

Doesn't look like this will ever get solved :Frown:

----------


## Muzza68

the thread may not, but the problem will be.........

I have half developed my emails from excel in half an hour, the script below works if you enter your server details (direct send via server, not Outlook draft):




```
Please Login or Register  to view this content.
```

----------


## Muzza68

For opening as a draft, try:



```
Please Login or Register  to view this content.
```


To close the draft after saving, remove the apostrophe before   .Close olPromtForSave

----------


## erprasannaa

Dear all.
please find attached file.
It may not to be fit your requirement.
But its quit simple and powerful

----------


## lifeisaspreadsheet

> Dear all.
> please find attached file.
> It may not to be fit your requirement.
> But its quit simple and powerful



Superlike!

Could you do me an additional favour. Is it possible to explain the process i.e. How does / will this work?

Thanks!

----------


## erprasannaa

In counmn A - You can type Name
In Column B  - You can type email Id
In Column C  -  You can type any addtional Requirements
In Counmn E -  You can type the CC mail Id
In Counmn F to K, You can select the attachment files if any

if you want to mondify the Body of the mail
Please go to macro and change in the code.

----------


## lifeisaspreadsheet

> In counmn A - You can type Name
> In Column B  - You can type email Id
> In Column C  -  You can type any addtional Requirements
> In Counmn E -  You can type the CC mail Id
> In Counmn F to K, You can select the attachment files if any
> 
> if you want to mondify the Body of the mail
> Please go to macro and change in the code.



Thank you for a prompt response!

Dear,

I’m sorry! I was doing something incorrectly in the version you shared. I tried it again and it is doing wonders. If I can have your solution modified it will be out of this world. It is a masterpiece!

A few things that I need your help on:

1. Is it possible to pick the subject line for every email from an xl file?
2. Is it possible to pick the text for every email from an xl file?
3. Is it also possible to add an excel table with data to the email body?

Please advise. If this can be done. I’ll be highly obliged. I’ve attached a spreadsheet with the way I get the data along with the required snapshot of the email.

You already are a life savor!

Please suggest....

----------


## erprasannaa

Dear 

Please find Revised Macro.

I don't thing i will not be able to crate Macro to insert table in mail body.

Please try and suggest for further improvement.

My Advice to you that you need to do somthing instead of asking exact your requirement.

----------


## lifeisaspreadsheet

[QUOTE=erprasannaa;3033995]Dear 

Thanks for your inputs and advice. I really appreciate that, but the irony is that I don't know macros and still learning. It is extremely important, and so was seeking help.

Just one thing, the revised solution is appending all different email details in the body into each email. My request is a bit different - I need to send a different text to each individual, the details of which are available on an excel file...so techically each row is for different recipient and has a different matter that needs to be relayed.

Regards,
Life

----------


## erprasannaa

Dear Life

Column C is for the Subject
every line you can type differnt subject and differnt Body of Email.
Please try once again.
I tested , it works fine.

----------


## lifeisaspreadsheet

Fantastic! That's why I love this place:-)

Everyone so understanding and helpful.

I'll try again on the same version ypu shared in your previous post, and seek your advice if I face any problems.

Thanks again!

----------


## erprasannaa

Please mark your thread as solved

----------


## lifeisaspreadsheet

> Please mark your thread as solved



Dear,

I haven't tested it again. I'm using my phone. I'll try it again once I get to work in the afternoon today.

Also, if this is successful which I think it will be, I'll still need to lookout for more advise to incorporate the table.

So I guess, I will not be able to mark it as Solved right now. Hope you understand!

Thanks!

----------


## erprasannaa

Hi Sonu,

do you have the data in a spread sheet to prepare the table?
if yes, you can make a pivot table and click show pages.
then it will come to individual sheet in the work book.
then you create a PDF document through macro.
then attach as s attachment in the mail.


think above point and coclude.

----------


## lifeisaspreadsheet

Sonu?

See attached. It dosen't seem to be working for me :Frown:

----------


## erprasannaa

copy the first line and past to next 10 lines
and run the macro
you can see the difference.

----------


## lifeisaspreadsheet

Sorry! May be I'm doing something wrong, as I'm getting the same result as shared in my screenshot. By any chance did you make any changes to the revised version you shared?

Apologies for bothering you, but due to some reasons it isn't working the way it is for you :Frown:

----------


## erprasannaa

If you run the macro are you getting muliple mail new mail item or not?

If yes , my macro can do as much.
If No, we will wait and see some other our excel expert can solve our problem.

----------


## lifeisaspreadsheet

I'm getting multiple mail items, but say if I add text in 3 rows:

First mail item shows - Text from row 1 in the body which is correct
Second shows - text from row 1 and 2 instead of only 2
Third shows - text from 1, 2 and 3 instead of only 3 and so on and so forth...

In any case your "macro" is still good. A minor tweak to it will surely add a star to it.

Let's wait and see if some expert  adds / modifies it which I'm very hopeful will :Smilie: .

Thanks and have a great day ahead!

----------


## erprasannaa

Dear Life:

Now I rectified the error.
Please find attached file and suggest

----------


## lifeisaspreadsheet

Thanks. This seems to be working fine now. 

Just one thing that I noticed the earlier version was also picking the name  Hi Abc from Column A, but this one doesnt.

----------


## event21

Hi -

Please try this one.

Regards,
Event

----------


## event21

Hi -

Body message in column based.

Regards,
Event

----------


## lifeisaspreadsheet

Dear Event,

Thank you for adding immense value to this solution!

Hi All,

For the benefit of our esteemed forum members I would like to outline a few things that this awesome solution (courtesy Event and erprasannaa) can accomplish:

1) You can compose multiple emails
 with it
2) Each text column helps you to add a different line of text
3) The best part it also has a table that can be directly inserted in the mail body
4) It has the To, CC and BCC fields
5) Exclusive subject field 
6) Option for attachments

In short, I would say it is a master piece.

Hope this comes handy to most of you!

Once again a big thanks to  Event and erprasannaa!

Regards,
Life

PS: For more details please refer to the thread, and we welcome any further advancements to it:-)

----------


## sonu_kumar444

2 problem in this coding i cant understand 

1. how to attached file path 
2. mess same in all mails long mess ( one by one column paste line it is long process 20-30 lines) i want all mails mess same & mail automatically macro run & mails send & save copy in sent folder in possible please suggest

----------


## erprasannaa

Dear Sonu,

YOu are not attached the file you are using.
I attached two files for you.
File name contains list files in a folder, helps to copy and paste the Path Name.
you can copy the full payth name and paste in attachement it will run.


test it and confirm.


regards
Prasanna.E

----------


## protonLeah

sonu_kumar444
Welcome to the Forum, unfortunately:

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

----------


## visha_1984

Hi Prasanna

your code working fine for me but ,I have one problem I just confirm is that open mail send automatically. :Smilie:

----------


## erprasannaa

go to last line in vba code

for send automatcially  put = .send.
for display                  put =.Display
for save as draft          put =.Save

----------


## visha_1984

Prasannaa
Thank u very much 

Good day.

----------


## sathishkm

You can use this code to send the emails from out look. But you have to include the body of the message in the code itself to send the mails. Just place all the names, emails in column A and B and in column C type "YES" for the mails you need to send. Now this will send the mails automatically to all the customers by your default email.

Sub Test1()
'Working in Office 2000-2013
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "C").Value) = "yes" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .to = cell.Value
                .Subject = "Hi"
                                'You can add files also like this
                '.Attachments.Add ("C:\test.txt")
                .Send  'Or use Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

----------


## arlu1201

Satishkm,

_Your post does not comply with Rule 3 of our Forum_ RULES. *Use code tags around code.* 

Posting code between  [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]  tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

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



*(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)*

----------

