Macro to send a simple email

[morrisgr]

I'm another one that needs help with sending an Email from within Excel.
I've checked many other postings without success.
Mine 'should' be simple (I think).

It involves sending an Email to Members when their subscriptions are due.
Using Excel 2007, Windows Live Mail.
I wish to extract 10 names and Email addresses from a Worksheet and send a simple Email to each saying "Your membership renewal was due on (date)"
I would prefer individual emails, otherwise one email would require using BCC address. (Which I gather may not be possible)
Any help greatly appreciated.
Thanks
Morris

 

[bensonsearch]

Hi,
Sending either group or individual emails are fine but, sending via outlook. via hotmail (windows live) doesnt really work in my experiance (unless its an account in outlook).

please see below and change as needed

 Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
      
    Dim user As Variant
   
    user = 'your email addresses
        
    
  
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = 'body of email
    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = user
        .Subject = "Subject goes here"
        .body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

 

[morrisgr]

Thanks 'bensonsearch'......worked fine.
One small improvement required!
str1..... holds the text for the 'body'......how can I make it refer to a cell in a different worksheet?
eg str1=Sheet("reminder")Cell A2
The text is in Cell A2 on Sheet "Reminder".

Then I've completed it.

Many thanks as always, this Forum never lets me down.
Morris

 

[bensonsearch]

Hi,

strbody = sheets("Reminder").range("A2").value

change as needed

 

[morrisgr]

Many thanks again.
I should have been able to work that out myself.
Morris

 

[bensonsearch]

not a problem, sometimes all you need is a small hand

 

[Excelboy_01]

Hello People
I also have a rather different but related problem and I was wondering if anyone of you could help me out. Basically, I have an excel worksheet with different information about different individuals. It also have a column (say Column Y ), which contains their respective emails addresses.

I would like to be able sort out (usings say AutoFilter) a group of people and send them an email (preferable personalized email) without going through copying/pasting individual email addresses from excel to outlook. Does anyone have an I dea how I can do this?

Thanks for your time and help in advance

 

[bensonsearch]

Hi ExcelBoy,

Really you should post a new thread if you have your own question. however yes this can be done with filters and looping. However you would need some form of copy and paste just to sort out the data.

Once you post your thread let me know and ill have a look for you.

cheers

 

[Excelboy_01]

Hi ExcelBoy,

Really you should post a new thread if you have your own question. however yes this can be done with filters and looping. However you would need some form of copy and paste just to sort out the data.

Once you post your thread let me know and ill have a look for you.

cheers

Thanks for your advice Bensonsearch!
I posted here because I saw that the problem was rather related and since people on this thread had worked on "affiliated" topic, would be better than starting a new thread. Nonetheless, as per your recommendation, I have created a new one which can be accessed from:
http://www.mrexcel.com/forum/showthread.php?t=584146

looking forward to your suggestions

Many thanks

Moses