Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to Mail spreadsheet.

    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

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Macro to Mail spreadsheet.

    What mail system do you use?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Macro to Mail spreadsheet.

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

    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

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

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

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

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

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

    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

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

    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.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

    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

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

    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?

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro to Mail spreadsheet.

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

    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 :<pre> Function User()
    User = Application.UserName
    End Function</pre>

    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 <pre> Sub SendMail()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    <font color=red>Signature = vbCrLf & vbCrLf & [UName] _
    & vbCrLf & "Dept : " & [Dept] _
    & vbCrLf & "Phone : " & [Phone]</font color=red>
    With objOMail
    .To = "Recipient"
    .Subject = "Subject"
    .Body = "laksdj;las fasf ds " <font color=red>& Signature</font color=red>
    .Attachments.Add ActiveWorkbook.FullName
    .Display
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub</pre>

    The additional code is shown in red.

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

  13. #13
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

    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.

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

    You can amend the code in Post 38432 above to something like the following : <pre>Sub SendMail()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    <font color=red>ActiveWorkbook.Sheets("Mail").Copy
    ActiveWorkbook.SaveAs FileName:=ActiveSheet.Name</font color=red>
    With objOMail
    .To = "Recipient1; Recipient2"
    .Subject = "Subject"
    .Body = "BodyText"
    .Attachments.Add ActiveWorkbook.FullName
    .Display
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub</pre>


    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

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Mail spreadsheet.

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •