Results 1 to 8 of 8

Thread: How To Send Outlook Email Using VBA

  1. #1
    Junior Member
    Join Date
    Feb 2013
    Posts
    10
    Rep Power
    0

    How To Send Outlook Email Using VBA

    I wants to e-mail many peoples.

    I have Excel file contain Name,email,Subject and Attachment

    Regards,
    Attached Files Attached Files

  2. #2
    Junior Member
    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    you may use below script, change the "Button1_Click()" with you Macro Name, .Display is used to view the mail before sending.


    Code:
    Option Explicit
    
    Sub Button1_Click()
    'Working in 2000-2010
        Dim OutApp As Object
        Dim OutMail As Object
        Dim sh As Worksheet
        Dim cell As Range, FileCell As Range, rng As Range
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set sh = Sheets("Sheet1")
    
        Set OutApp = CreateObject("Outlook.Application")
    
        For Each cell In sh.Columns("C").Cells.SpecialCells(xlCellTypeConstants)
    
            'Enter the file names in the D:Z column in each row
            Set rng = sh.Cells(cell.Row, 1).Range("D1:Z1")
    
            If cell.Value Like "?*@?*.?*" And _
               Application.WorksheetFunction.CountA(rng) > 0 Then
                Set OutMail = OutApp.CreateItem(0)
    
                With OutMail
                    .to = cell.Value
                    .Subject = "Testmail"
                    .Body = "Hi " & cell.Offset(0, -2).Value
    
                    For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                        If Trim(FileCell) <> "" Then
                            If Dir(FileCell.Value) <> "" Then
                                .Attachments.Add FileCell.Value
                            End If
                        End If
                    Next FileCell
    
                    .Display  'Or use Send
                End With
    
                Set OutMail = Nothing
            End If
        Next cell
    
        Set OutApp = Nothing
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        
    End Sub
    Last edited by Excel Fox; 02-23-2013 at 12:14 AM. Reason: Code Tags

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Did you check the template given here? http://www.excelfox.com/forum/f18/se...email-ids-304/
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Feb 2013
    Posts
    10
    Rep Power
    0
    Thanks.

    but i received massege before sending every mail Allow / Deny .

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Well, this is not recommended, but you can change the option of programmatic access in the trust center (Outlook 2007) to 'Never warn me about suspicious activity', and you should not get that message again.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Feb 2013
    Posts
    10
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    Well, this is not recommended, but you can change the option of programmatic access in the trust center (Outlook 2007) to 'Never warn me about suspicious activity', and you should not get that message again.

    Yes, but i want through VBA Code when needed

  7. #7
    Junior Member
    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    you can choose allow, then select 10 minutes, if you dont want to change the settings of the trust center, in that case, you are allowing to connect the MAcro atleast 10 minutes to your outlook, and never see that message for the next 10 minutes

  8. #8
    Junior Member
    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    Hi mfaisalrazzak

    What version of Excel are you using?

    What version of Outlook are you using?

    Post your workbook with the Code you've tried...we'll try to make it work.

Similar Threads

  1. Replies: 12
    Last Post: 12-10-2019, 09:56 PM
  2. Replies: 17
    Last Post: 07-15-2013, 09:56 PM
  3. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  4. Send Outlook Email With Word Document
    By Murali K in forum Excel Help
    Replies: 2
    Last Post: 06-27-2012, 08:42 PM
  5. Send Lotus Notes Email Using VBA
    By ramakrishnan in forum Excel Help
    Replies: 1
    Last Post: 09-08-2011, 09:00 AM

Posting Permissions

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