newbie on excel email

[Fred Evans]

Hello Chaps-- I am trying to get my mind (and my code) around using excel to
send an email to everyone on a list of email addresses which is a single column
on a worksheet.
Thanks chaps for previous hints but I need some very basic information.
Firstly.
I can quite easily get the email address and send a blank mail to myself.

Where can I find ( or can someone give me ) the syntax for telling outlook
that it must send an email using a specific form

My plan is to set up a new email template in outlook and then somehow get that email
template sent through to the reciepient.

I have set up a variety of signatures which will form the letter that I hope to send
out

All help sincerely appreciated

Fred

 

[Perpa]

Hello Chaps-- I am trying to get my mind (and my code) around using excel to
send an email to everyone on a list of email addresses which is a single column
on a worksheet.
Thanks chaps for previous hints but I need some very basic information.
Firstly.
I can quite easily get the email address and send a blank mail to myself.

Where can I find ( or can someone give me ) the syntax for telling outlook
that it must send an email using a specific form

My plan is to set up a new email template in outlook and then somehow get that email
template sent through to the reciepient.

I have set up a variety of signatures which will form the letter that I hope to send
out

All help sincerely appreciated

Fred

Fred,
Are you comfortable using VBA macros? I have solution to send an email to a column list of email addresses.
I don't quite understand what you mean by "I have set up a variety of signatures which will form the letter
that I hope to send". Perhaps you can explain that. I did a very similar thing to email a monthly schedule out to a
changing list of email addresses. One of the macros saves the active file as a macro enabled file and as a pdf file.
The pdf file becomes the email attachment. There are 3 macros.

I am running Excel 2007 on Windows 7. What are you using?
Perpa

 

[Fred Evans]

Macros !! yes I am learning them and could say i am still a novice but I am comfortable
What i cant find is the syntax for telling VBA to send an email- could you point me to some
reading on the subject.

Thanks for your input. If you dont mind sharing your code- i would like to have a look at
your code especially if I cannot find the syntax that i am looking for

The variety of signatures bit? My logic is as follows. If something is sent out as an attachment
the reciepient ( depending on his email setup ) may have to click to open the attachment.
However If the message is part of the signature- it opens as the reciepient hovers over the
message. My messages are generally oneliners and it is important to me that the message
is presented to the reciepient without him having to do anything else. Therefor I am reluctant
to send out attachments because I find that often messages are sent to me and I dont
want to mess around still with opening attachments so if the sender is not on my immediate
list I usually just delete it or put it away in a pending file which may only be opened in a
month time (if ever). I will send you a private message as an example.
Best regards
and have a happy day
fred

 

[Perpa]

Fred,
Go to Ron de Bruin Excel Automation at the link below. There are many examples, and he explains how to do many
things to automate the Excel/Outlook connection.

Tips for the Outlook object model examples

Ron does show how to add a message to the 'BODY' of your email which is what you are trying to do.
That should get you going. That portion of the code will look something like the following:

        With OutMail
            .To = Sheets("Sheet1").Range("Z7").Value     'Change to cell where all email addresses are stored
					      'see comments in 'MakeListOfAddresses' macro
            .CC = ""
            .BCC = ""
            .Subject = "Typical File Name"
            .Body = cells(15,4)			'Change this location to suit
            '.Attachments.Add PDFfileName
            .Send
        End With

Put your message in some cell, say D15 =Chr(10) & "Automated eMail" & Chr(10) & "Fred Alan"

Below is the macro I created to make a my list of email addresses from a column of email addresses
which can be added to your '.TO' . My list was in column Z, beginning in row 9.You can change 'Z7'
to whatever cell you want. This information should get you going.

To copy and paste macros into the General code module do the following:
Right click the tab for your main sheet then click 'View Code'.
Paste the macros into the empty General code window that opens up.
Close the code window to return to your sheet.
'Save As' a macro-enabled Workbook.
Close the file and then reopen, you should be asked if you want to enable macros…
select the option to enable macros…the default won't allow macros to run.
Now you can run the macros by pressing 'Alt+F8', select the macro name, then 'Run'
(On my computer, I set the macro up to run using an ActiveX command button... it's
easier to just push one button than using the 'Alt+F8' method.)
Perpa

Private Sub MakeListOfAddresses()
'Make  List of all email addresses and put them all in cell Z7
    Dim w As Long
    Dim LR As Long
    Dim t As String
    Dim Rng As Range, c As Range
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    LR = Range("Z9").End(xlDown).Offset(1, 0).Row      'Z9' is the top of the email list, change to suit
    Set Rng = Range("Z9:Z" & LR)
    
    'Trim - replaces all multiple spaces with a single one,
    'as well as removes spaces from the left & right of the cell value
        With Rng
            .Value = Application.Trim(.Value)
        End With
    
        For Each c In Rng
            If c <> "" Then
                t = t & c & ";"                    
            Else
                t = Left(t, Len(t) - 2)            'Removes the last semicolon
                c.Value = t
                t = ""
            End If
        Next c
    Cells(7, 26) = Cells(LR, 26)              'Copies the list to Z7
    Cells(LR, 26).ClearContents           'Clears list from the last row of column Z
			            'since the last row may change and we need
			            'the information to be in one known cell location
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub