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

    eMail Macro (Excel 97, Outlook 98)

    I use the following macro to create a mail out with an attachment. At the end of each .BODY line I would like to make them start a new line and not continue and do a wrap.

    Sub Macro1()
    Application.Dialogs(xlDialogSendMail).Show
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    With objOMail
    .To = "Daniel Reyes"
    .Subject = "Announcement"
    .Body = "The following are no longer employed at TABS Direct."
    .Body = .Body & " Please do not allow Them access to the facility "
    .Body = .Body & " except as a visitor through the Receptionist."
    .Attachments.Add ActiveWorkbook.FullName
    .Body = .Body & " "
    .Body = .Body & " Kay Hand"
    .Body = .Body & " HR Rep."
    .Display
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub

    Also at the .Body = .Body & " Kay Hand" & .Body = .Body & " HR Rep." How can I change the Font, size, color?

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

    Re: eMail Macro (Excel 97, Outlook 98)

    You could us the VB constant VbCRLf, which inserts a carriage return and line feed.

    e.g. <pre>.Body = .Body & VbCrLf & "Additional text message etc...."</pre>

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: eMail Macro (Excel 97, Outlook 98)

    Daniel,

    I sent you an email with an XL file attached that I use for distributing XL files. I'm qurious as to the code you mentioned. I tried envoking it and encounted the following error message:

    Compile Error:
    User-defined type not defined

    This error was noted on the 2nd line of code: Dim objOLook As New Outlook.Application
    and each Dim statement thereafter.

    Is this an XL setup issue?

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

    Re: eMail Macro (Excel 97, Outlook 98)

    This was given to me by Andrew C. and others

    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 refernce to your version of Outlook, and make sure it is selected (checkbox ticked). Hope this helps.

    I am still trying to figure out how to change the font/size/color for the last two .body lines at the bottom. Then I plan to create a command button (for this macro) on the spreadsheet. With luck it will then gray out so the people that get it will not be able to use the command button.

    Sub Macro1()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    With objOMail
    .To = "Daniel Reyes"
    .Subject = "Announcement"
    .Body = "The following are no longer employed at TABS Direct."
    .Body = .Body & vbCrLf & " Please do not allow them access"
    .Body = .Body & vbCrLf & " except as a visitor."
    .Attachments.Add ActiveWorkbook.FullName
    .Body = .Body & vbCrLf & " "
    .Body = .Body & vbCrLf & " "
    .Body = .Body & vbCrLf & " Kay Hand"
    .Body = .Body & vbCrLf & " HR Rep."
    .Display
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub

Posting Permissions

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