Excel vs Outlook macro

[Clefebvre]

Is it possible to do a macro to copy a cell in excel and paste that cell in outlook and return to excel

Thanks

 

[verluc]

On 2002-05-31 05:16, dk wrote:

Now How cant i add a attachement file in the macro

You can add attachments. Use this line before you send it:-

olMail.Attachments.Add "C:Your folderYour File.xls"

HTH,
Dan

Hi Dan,

I have add your code above in my macro.When I send a file as an attachment,then the receiver of this e-mail sees this file at the top of his screen.Is there a possibility that this file stays in the left undercorner so like you send it mannualy?
Thanks

 

[dk]

Hi Verluc,

I'm not sure about this. When I send an email (using Outlook XP) the attachment always appears in the same place (in a separate bar just below the subject line).

A few questions:-

Which version of Outlook are you using?

When you run the macro what sort of message is created i.e. HTML, plain text, RTF?

Is the type of message created different from the type you create when manually attaching a file?

 

[verluc]

On 2002-09-11 02:14, dk wrote:
Hi Verluc,

I'm not sure about this. When I send an email (using Outlook XP) the attachment always appears in the same place (in a separate bar just below the subject line).

A few questions:-

Which version of Outlook are you using?

When you run the macro what sort of message is created i.e. HTML, plain text, RTF?

Is the type of message created different from the type you create when manually attaching a file?

Hi Dan,

Can I send you via e-mail the two files: one
send via my macro and one send manually?
so you can see the difference.
Thanks in advance

 

[dk]

I've sent you a private message with my email address.

 

[Automato]

Hi. I found this thread whilst googling around to try and find out how to do something similar.

I managed to get something vaguely OK using this:

Hi,

Try this code.. .. ..

Sub Test()
Dim olApp As Object, olMail As Object

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
olMail.To = "Anyone@anywhere.com"
olMail.Body = Range("a1")   'Change to suit your needs

'To display the message use this
olMail.Display

'or to send the message use this
'olMail.Send

'Reactive Excel
AppActivate "Microsoft Excel"


End Sub

Let me know if this suits your needs,

Dan

but really I need to do something a little more complex.

There are two problems I have.

1. It's more than one cell that I need to cut and paste into the e-mail and
2. the length of the range will differ.

I couldn't even select a range. I tried:

olMail.Body = Range("a1:a50")

to select all of the range even though some of the cells would be blank.

but it gave an error.

My current workaround is the rather longwinded

MyVar = Range ("a1")
Myvar = Myva + Ranger ("a2")
Myvar = Myvar + Range ("a3")
...etc. ...etc. ...etc. up to a50
...
similar to above code except for
...
olMail.Body = Myvar

Which works but feels wasteful especially as there might only be a1 to a5 filled at any given time. The spreadsheet I'm currently using it on is only a basic list generator so it doesn't matter that much for that one but there are similar macros I would like to write for other spreadsheet where a range of, say, A1:F50 would be used where it would be even more impractical (and give me very achy hands!).

Any help greatly appreciated.

 

[DonkeyOte]

see here for similar question:

http://www.mrexcel.com/forum/showthread.php?t=351519

In the above link there is an example as to how to loop the range adding each cell to the body string one at a time -- you could add an extra test to ensure cell.value is not blank before adding to the body string.

thanks,

 

[DonkeyOte]

See also here for dk's "legendary" post on this topic:

http://www.mrexcel.com/forum/showthread.php?t=8086

 

[afguard17]

This code is excellent for sending out one message at a time. Could this be amended to run down a list of email addresses and set up say 20 emails at once to be sent?

I have a list of clients that I need to send various invoice numbers to and I would love to be able to have a column with their invoice number and then a column with their email address and run the macro and set up all 20 unique emails at once. I'm sorry if this has been discussed, but I looked at the looping link above but it was for different code.

If I figure it out before anyone else I'll post my results.

 

[drew0803]

I have a related question:

I am looking for a code that can send emails from Outlook pulling information from multiple columns in Excel. The columns in excel are: Name, Email Address, Company Name. The email to be sent is a standard intro email with the subject "Hello." The email has blanks to be filled in by the excel columns.

Thanks for the help.
Drew

 

[raulabusada]

Modified the original code to copy all the adresses filled in the excel worksheet to the To: field. Can be changed to add them to diferent fields.

Sub Test()
Dim olApp As Object, olMail As Object
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
Dim emails As Integer
emails = 1
Do While emails < 85' put the number of cells that you have filled with email adresses
olMail.To = olMail.To + ";" + Range("a" & emails) 'Change to suit your needs
emails = emails + 1
Loop
'To display the message use this
olMail.Display
'or to send the message use this
'olMail.Send
'Reactive Excel
'AppActivate "Microsoft Excel"

End Sub