VBA - Need to add functionality to open sheet and refresh data before sending

[jaihawk8]

I have an Excel Workbook set up already that automatically attaches a specific file for each Sales Rep and e-mails it to them when I run the macro. I have now modified their individual files so that they are indexing information from another file. What I would like to do, is add functionality to this existing VBA to refresh each one of the files before it e-mails it, rather than me having to go in and refresh each one manually before running this script. Is this possible? This is one of the scrips for Austin that I have currently.


Sub Austin()


Dim edress As String
Dim subject As String
Dim message As String
Dim fileName As String
Dim outlookapp As Object
Dim myAttachments As Object
Dim path As String
Dim lastrow As Integer
Dim attachment As String
Dim x As Integer


x = 6
y = 2
Z = 3


Do While Sheets("Austin").Cells(x, 1) <> ""


Set outlookapp = CreateObject("Outlook.Application")
Set outlookmailitem = outlookapp.createitem(0)
Set myAttachments = outlookmailitem.Attachments


path = Sheets("Austin").Range("B2")
edress = Sheets("Austin").Cells(x, 1)
subject = Sheets("Austin").Cells(x, 2)
fileName = Sheets("Austin").Cells(x, 3)


attachment = path & fileName


outlookmailitem.To = edress
outlookmailitem.cc = Sheets("Main").Range("B15")
outlookmailitem.bcc = ""
outlookmailitem.subject = subject
outlookmailitem.body = Sheets("Austin").Cells(Z, 2)


myAttachments.Add (attachment)
outlookmailitem.display
outlookmailitem.send


lastrow = lastrow + 1
edress = ""


x = x + 1


Loop


Set outlookapp = Nothing
Set outlookmailitem = Nothing




End Sub

 

[JackDanIce]

Untested, try:

Sub Austin()

    Dim x           As Long
    Dim arr()       As Variant
    Dim oApp        As Object
    Dim oMailItem   As Object
    Dim oAttach     As Object
    Dim wkb         As Workbook
    
    With Sheets("Austin")
        x = Application.Max(.Cells(.Rows.Count, 1).End(xlUp).Row, 6)
        If x > 5 Then
            arr = .Cells(6, 1).Resize(x - 5, 3).Value
        Else
            MsgBox "No values found", vbExclamation
            End
        End If
        
        Set oApp = CreateObject("Outlook.Application")
        
        For x = LBound(arr, 1) To UBound(arr, 1)
            Set oMailItem = oApp.createitem(0)
            Set oAttach = oMailItem.Attachments

            Set wkb = Application.Open(.Cells(2, 2).Value & arr(x, 3))
            With wkb
                .RefreshAll
                .Close True
            End With
            Set wkb = Nothing
            
            oMailItem.To = arr(x, 1)
            oMailItem.cc = .Cells(15, 2).Value
            oMailItem.subject = arr(x, 2)
            oMailItem.body = .Cells(3, 2).Value
            
            oAttach.Add .Cells(2, 2).Value & arr(x, 3)
            oMailItem.display
            oMailItem.send
        Next x
    End With
    
    Erase arr
    Set oAttach = Nothing
    Set oMailItem = Nothing
    Set oApp = Nothing
    
End Sub