Results 1 to 4 of 4

Thread: How to send emails from excel vba (with attachment) using outlook

  1. #1
    Junior Member
    Join Date
    Jun 2016
    Posts
    2
    Rep Power
    0

    How to send emails from excel vba (with attachment) using outlook

    Hi there,

    I have not done any vba coding before and would like to shout out to the kind souls out there who are willing to share the VBA codes with me..

    Below are what I would like to see in the email automation:

    - Sheet 1: for me to enter all the email addresses i wanna Bcc to and their names in two columns
    * i just want to send to an email address and Bcc to the rest of the contacts..

    Sheet 2: for typing out the email subject line, body message and who i wanna send to/ cc/bcc etc.
    * as i type in the cells, the coding will be updated too

    - As i add in another new email contact, the coding will also be updated

    - i want to attach the same attachment for all the email contacts

    - a 'send email' button to be created in 'Sheet 2' and upon clicking, it opens up the outlook box and i can proceed to send via outlook

    Really hope someone could show me the codes for the above, greatly appreciate all help!

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Hi finding_dory,

    Welcome to ExcelFox

    Did you try using this
    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

  3. #3
    Junior Member
    Join Date
    Jun 2016
    Posts
    2
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    Hi finding_dory,

    Welcome to ExcelFox

    Did you try using this
    Hey Excel Fox, thanks for the reply! I used the below codes but it doesnt attach any files to my email.. could you provide me with codes that command for file attachment and where in my codes should i insert them? Thank you once again!

    VBA Code I used:
    Code:
    Private Sub CommandButton2_Click()
    
    Dim OutlookApp As Outlook.Application
    Dim MItem As Outlook.MailItem
    Dim cell As Range
    Dim Subj As String
    Dim EmailAddr As String
    Dim Recipient As String
    Dim Msg As String
    Dim objOutlookAttach As Outlook.Attachment
    
    'Create Outlook object
    Set OutlookApp = New Outlook.Application
    
    'Loop through the rows
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeVisible)
    If cell.Value Like "*@*" Then
    EmailAddr1 = EmailAddr1 & ";" & cell.Value
    End If
    Next
    
    For Each cell In Columns("E").Cells.SpecialCells(xlCellTypeVisible)
    If cell.Value Like "*@*" Then
    EmailAddr2 = EmailAddr2 & ";" & cell.Value
    End If
    Next
    
    For Each cell In Columns("H").Cells.SpecialCells(xlCellTypeVisible)
    If cell.Value Like "*@*" Then
    EmailAddr3 = EmailAddr3 & ";" & cell.Value
    End If
    Next
    
    'Create Mail Item and view before sending
    Set MItem = OutlookApp.CreateItem(olMailItem)
    With MItem
    .To = EmailAddr2
    .BCC = EmailAddr1
    .CC = EmailAddr3
    .Subject = Sheet2.Cells(5, 2).Value
    .Body = Sheet2.Cells(7, 2).Value
    .Display
    End With
    
    End Sub




    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 06-11-2023 at 01:17 PM. Reason: Added Code Tags

  4. #4
    Junior Member
    Join Date
    Feb 2019
    Posts
    1
    Rep Power
    0
    i have tried diffrent codes for this in VBA . what if the attachement or attachements are located in a shared drive?
    Last edited by jay; 02-09-2019 at 08:49 AM.

Similar Threads

  1. Replies: 0
    Last Post: 08-04-2015, 11:08 PM
  2. Replies: 1
    Last Post: 06-28-2014, 11:31 AM
  3. Replies: 1
    Last Post: 12-23-2013, 10:09 PM
  4. Replies: 6
    Last Post: 06-05-2013, 11:33 PM
  5. Export outlook emails to Excel code Error
    By jamilm in forum Outlook Help
    Replies: 2
    Last Post: 02-22-2013, 03:48 PM

Tags for this Thread

Posting Permissions

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