Mail from sheet

  • Re: Mail from sheet


    Do-able. Are you using Lotus Notes, Outlook or Outlook Express?

  • Re: Mail from sheet


    Can you achieve what you're after with the SendMail method? This is a quick and easy way to email through Outlook and Outlook Express. I don't think you can specify 'cc' recipients with this - they all have to be 'to'.


    Otherwise, it's slightly more complicated, but nothing too bad.

  • Re: Mail from sheet


    Hi Edwin,


    This should help you going:


    [vba]


    Sub send_mail()
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim cell As Range
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    'If cell.Offset(0, 1).Value = "yes" Then
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = cell.Value
    .CC = cell.Offset(0, 3).Value & ";" & cell.Offset(0, 4).Value & ";" & cell.Offset(0, 5).Value
    .Subject = cell.Offset(0, 8).Value
    .Body = cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
    cell.Offset(0, 6).Value & vbNewLine & vbNewLine & _
    cell.Offset(0, 7).Value & vbNewLine & vbNewLine & _
    " Thanx & Best Regards" & vbNewLine & _
    " Type your name here" & vbNewLine
    .Attachments.Add cell.Offset(0, 2).Value
    .Display ' type send if you want directly to send
    End With
    Set OutMail = Nothing
    ' End If
    Next cell
    cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
    End Sub


    [/vba]


    You have to select Microsoft Outlook Object Library 9.0 (or any later version) from References menu.


    I have attached excel which will show you the format in which you have enter data. This code I have adopted from the site which I have already mentioned to you.


    Hope this what you are looking for.


    HTH.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!