Macro to Mail spreadsheet.

Topic

New Reply

I have a spreadsheet that I want to mail to a group of people. I want to have a clerk do this everyday. I would like to create a macro. When it is start it will do File, Sent to, Mail Recepient. Then in the body it will enter a brief message and stop there. They will then add the names of the people and send the message with the spreadsheet attached. Does anyone have any ideas on how to do this? I am not very good with the Macro recording feature.

Thank you

Reply | Quote

Replies

What mail system do you use?

Reply | Quote

Daniel,

If you’re willing to settle for creating a new mail item, but minus the body text, you can just use something like this:

Public Sub SendTheMail()
‘Excel 2000
ActiveWorkbook.SendMail (“”)
End Sub

If you need to add a brief message to the body of the e-mail, then you’ll need to start dealing with creating an instance of Outlook (assuming your e-mail app is Outlook), and controlling Outlook programatically to assign the body text – a lot more complicated than the simple approach above(!).

Gary

Reply | Quote

Hi,
Is it at all possible to add coding to this thread to save one sheet of the workbook as a new workbook and then attach the new workbook to the e-mail.

Reply | Quote

You can amend the code in Post 38432 above to something like the following :

Sub SendMail()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    ActiveWorkbook.Sheets("Mail").Copy
    ActiveWorkbook.SaveAs FileName:=ActiveSheet.Name
    With objOMail
            .To = "Recipient1; Recipient2"
            .Subject = "Subject"
            .Body = "BodyText"
            .Attachments.Add ActiveWorkbook.FullName
            .Display
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
End Sub

What that does is copy a sheet named Mail (which you can change to whatever is appropriate) to a new workbook, saves that workbook with the sheet name as File name (you may wish to change that also) and attaches it to the email.

Andrew C

Reply | Quote

If you using Office 2000, and Outlook is your email program the following code should launch Outlook and attach the active workbook.

For this code to function you will need to set up a reference to the Outlook object in your application. To do this goto the VB editor and select Tools, References.. and scroll down the list until you reach Microsoft Outlook 9.0 Object Library, and click the check box to select it.

Sub SendMail()
Dim objOLook As New Outlook.Application
Dim objOMail As MailItem
Set objOLook = New Outlook.Application
Set objOMail = objOLook.CreateItem(olMailItem)
With objOMail
.To = “Recipient1; Recipient2”
.Subject = “Subject”
.Body = “BodyText”
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set objOMail = Nothing
Set objOLook = Nothing
End Sub

It should also be possible to enter the recipients’ names by selecting from the Outlook Contacts List (which you culd access via the procedure), so that the message could be automatically sent, by putting .Send after (or instead of) .Display .

Hope that helps

Andrew C

Reply | Quote

I am using Excel97 and Outlook98. Will try to get this working.

Reply | Quote

I keep getting “User-defined type not defined” on the the first “DIM objOLook As New objOLook.Application” What am I doing wrong?

Reply | Quote

Daniel,

It should work for you but you need to ensure the reference to the outlook object is set up correctly for the particular workbook. The reference should be Microsoft Outlook x.0 Object Library, where x is probably 8 for your version, but use whatever Outlook Library is available.

Regards,

Andrew

Reply | Quote

When I to to Excel/Macros/VB/Tools/Ref. I have Outlook 8.0 Obj. library checked. I still get the same problem. If I take out the objOutlook or Outlook from “Outlook.Applications” it goes a step further. I am not a programmer but I am starting to understand what you are saying. Will try again.

Reply | Quote

I tested it from XL97 using Outlook 97 and everything worked fine. I don’t think Outlook 98 should be any different.

Just ensure that Outlook is fully installed.

Andrew

Reply | Quote

I got the macro to work in my spreadsheet. I could still use a little help in the .Body section. I want to add about three lines worth of information. Then add the attactment after the body. My problem is that I then want to add more after this in the body. I guess you could say I want to add a signature, dept and phone number of the person sending the message.

.Subject
.Body = ” laksdj;las fasf ds
.Attachments.Add
.Body = ” add more information here”
.Display

Any ideas?

Reply | Quote

Hi,
If you want to append text to the body of the message rather than replace what’s already there, you should be able to do it with:
.Body = .Body & “more text here”
Hope that helps.

Reply | Quote

If you have a number of users you could include a mini database in the workbook which would include a Name, Dept and Phone No for each user. It could be set up in a range in a separate worksheet and hidden from view if required. Lets say you created and named this range as Users. Next create a single cell range and call it UName, then another beside it called Dept and another beside that called Phone. If all your users have their own installation of Excel it should be easy to get the user name from the Application.UserName property. If you do use this approach you need to ensure the names in your database match the names stored on each PC as the UserName. You can check the names by going to Tools, Options and on the General tab where you will find the Username. Include the following Function the project :

    Function User()
        User = Application.UserName
    End Function

Then in the cell you named UName enter the formula =User(), then in Dept enter =VLOOKUP(Uname,Users,2,False) and in PhoneNo enter =VLOOKUP(Uname,Users,3,False).

You should then change your email code to

    Sub SendMail()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    Signature = vbCrLf & vbCrLf & [UName] _
                & vbCrLf & "Dept : " & [Dept] _
                & vbCrLf & "Phone : " & [Phone]
    With objOMail
        .To = "Recipient"
        .Subject = "Subject"
        .Body = "laksdj;las fasf ds " & Signature
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub

The additional code is shown in red.

If you cannot use the UserName property you will need to get the user

Reply | Quote

I keep getting a Compile-errors – User-defined not defined

Dim objOLook As New Outlook.Application
Dim objOMail As MailItem
Set objOLook = New Outlook.Application
Set objOMail = objOLook.CreateItem(olMailItem)
With objOMail
.To = “Recipient”
.Subject = “Subject”
.Body = “laksdj;las fasf ds ”
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set objOMail = Nothing
Set objOLook = Nothing
End Sub

Reply | Quote

Hi,
Try changing your code to this:
Dim objOLook As New Outlook.Application
Dim objOMail As Outlook.MailItem
Set objOMail = objOLook.CreateItem(olMailItem)
With objOMail
.To = “Recipient”
.Subject = “Subject”
.Body = “laksdj;las fasf ds ”
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set objOMail = Nothing
Set objOLook = Nothing

Hope that helps.

Reply | Quote

You need to set up a reference to Microsoft Outlook in your project.

In the VBE, go to Tools, References and scroll down until you find a reference to your version of Outlook, and make sure it is selected (checkbox ticked).

Andrew C

Reply | Quote

Andrew
I’ve been trying to use different fonts in an e-mail sent from Excel (98) for the signature block and within the text. I cannot find any further info on this, can you help me?

Rgds
Jim

Reply | Quote