Multiple attachments to send in outlook at one shot

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
276
Hi everyone,

Here i am trying to send multiple attachments at one shot but as i compiled and check its exiting at the point of attachment at the send time
Any one suggest me with what changes i should do????
Here is my code
Code:
Sub Mail()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim path, signature, emp_name, subject, body As String
Dim file As String
Dim x As Long
On Error GoTo ExitSub
lastrow = Sheets("Mail").Cells(Rows.Count, "A").End(xlUp).Row
For x = 2 To lastrow
        path = Cells(x, "A").Value & "\" & Cells(x, "B").Value
        emp_name = Cells(x, "C").Value
        subject = Cells(x, "D").Value
        body = Cells(x, "E").Value
        signature = Cells(x, "G").Value
        Set myApp = New Outlook.Application
        Set mymail = myApp.CreateItem(olMailItem)
        mymail.To = Cells(x, "F").Value
    If path <> "" Then
        If (Cells(x, "D").Value) <> "" Then
            With mymail
                .subject = subject
                .Attachments.Add (path)
                .body = "Hi " & emp_name & "," & vbCrLf & body & vbCrLf & vbNewLine & "Regards," & vbCrLf & signature
                .Display
                .send
            End With
        Else
            MsgBox ("Please Enter Mail ID")
            Exit Sub
        End If
    Else
        MsgBox ("Please Path and File Name")
    End If
Next
Set myApp = Nothing
Set mymail = Nothing
ExitSub:
End Sub
Thanks in advance
Dhruva
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
276
Hi Everyone,
If i run the above code it should send the mails to all the recipients with the specific attachments, but as of now it is sending only one mail and gets exited at the 2nd time run.
Can anyone suggest me what changes i need to make ??????????

It gets exited at the highlighted row

Hi everyone,

Here i am trying to send multiple attachments at one shot but as i compiled and check its exiting at the point of attachment at the send time
Any one suggest me with what changes i should do????
Here is my code
Rich (BB code):
Sub Mail()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim path, signature, emp_name, subject, body As String
Dim file As String
Dim x As Long
On Error GoTo ExitSub
lastrow = Sheets("Mail").Cells(Rows.Count, "A").End(xlUp).Row
For x = 2 To lastrow
        path = Cells(x, "A").Value & "\" & Cells(x, "B").Value
        emp_name = Cells(x, "C").Value
        subject = Cells(x, "D").Value
        body = Cells(x, "E").Value
        signature = Cells(x, "G").Value
        Set myApp = New Outlook.Application
        Set mymail = myApp.CreateItem(olMailItem)
        mymail.To = Cells(x, "F").Value
    If path <> "" Then
        If (Cells(x, "D").Value) <> "" Then
            With mymail
                .subject = subject
                .Attachments.Add (path)
                .body = "Hi " & emp_name & "," & vbCrLf & body & vbCrLf & vbNewLine & "Regards," & vbCrLf & signature
                .Display
                .send
            End With
        Else
            MsgBox ("Please Enter Mail ID")
            Exit Sub
        End If
    Else
        MsgBox ("Please Path and File Name")
    End If
Next
Set myApp = Nothing
Set mymail = Nothing
ExitSub:
End Sub
Thanks in advance
Dhruva
 
Last edited:

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
276
Hi everyone,

I have a question that can we send mails from different Mail Id's with having user name and passwords in an excel sheet.

Regards,
Dhruva
 

Watch MrExcel Video

Forum statistics

Threads
1,102,774
Messages
5,488,770
Members
407,657
Latest member
Sassy5

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top