Need assistance with using email with VBA

[kconn]

Hi there!

I am relatively new to a company that are using macros as part of some key processes, I have been tasked with fixing them whenever there is an issue because my predecessor created the macro, but my VBA knowledge is minimal so any help with this would be massively appreciated!

The file in question baisically sends out the spreadsheet as an attachment to the same email everytime as well as printing off a hard copy.

There have been some new customer service reps (main users of this file) joining the company and for some reason the email and printing part of the macro have not been working for them only.

Below is the code concerned:

    Range("A1").Select
    
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object
    Set wb1 = ActiveWorkbook
    If Val(Application.Version) >= 12 Then
        If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
                   "be no VBA code in the file you send. Save the" & vbNewLine & _
                   "file as a macro-enabled (. Xls) and then retry the macro.", vbInformation
            Exit Sub
        End If
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    ' Make a copy of the file.
    ' If you want to change the file name then change only TempFileName variable.
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Pickup Request for " & Range("D13").Value & Range("D15").Value & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, _
                                   Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
    Set OutApp = CreateObject("Outlook.Application")
    
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
   ' Change the mail address and subject in the macro before you run this procedure.
    With OutMail
        .To = "[EMAIL="despatch.ak@company.co.nz"]despatch.ak@company.co.nz[/EMAIL]"
        .CC = "[EMAIL="shelley.surname@company.co.nz"]shelley.surname@company.co.nz[/EMAIL]"
        .BCC = ""
        .Subject = "Product Pickup - " & Range("D13").Value & Range("D15").Value
        .Body = "This form has been also been printed, please give it to the appropriate driver for collection ASAP."
        .Attachments.Add wb2.FullName
        ' You can add other files by uncommenting the following line.
        '.Attachments.Add ("C:\test.txt")
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        .Send
    End With
    On Error GoTo 0
    wb2.Close SaveChanges:=False
    ' Delete the file.
    Kill TempFilePath & TempFileName & FileExtStr
    Set OutMail = Nothing
    Set OutApp = Nothing
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
   Range("A1").Select
   MsgBox ("Document Emailed to [EMAIL="Despatch.ak@company.co.nz"]Despatch.ak@company.co.nz[/EMAIL] & [EMAIL="Shelley.surname@company.co.nz"]Shelley.surname@company.co.nz[/EMAIL]")

Thanks!

kconn

 

[Michael M]

Does it give an error ?
Does it do notjhing at all ??

Make sure the new users have enabled macros when the open the workbook

You could run the code on their machine manually by using F8 and see if / where it doesn't do as it's supposed to.....and then post back with more info

 

[michael casserly]

Hello there,

My problem is similar to the one above, I believe.

I have a workbook created that records supplier performance based on feedback input on various department worksheets, this input data generates a list of preferred suppliers, via formula's conditional formatting etc.
Being new, very new, to <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> I really need assistance with the following:

1. At the moment I have 3 events in mind;
a, A new Supplier is added to the system.
b, A supplier/or number of suppliers are due/or overdue an audit.
c, A supplier has dropped off the "A" list, (supplier has dropped in status)
2. What I would like is, have a macro monitor certain cell values, (at a certain time). When and if the criteria in a,b,& c are met, the macro sends an automated email response to a predetermined list of recipients, either to, cc or bcc.

I have a table of system users, which is used by another code to identify login and password details. I am guessing if I add their email addresses to this table, the new code can use a vlookup to pull the addresses.

Again, I'm lost in this big pool of <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>, code and macro stuff, but impressed enough by what I've seen so far, to really want to use it (and Learn). I cant do it without help............

Please

M

 

[kconn]

Does it give an error ?
Does it do notjhing at all ??

Make sure the new users have enabled macros when the open the workbook

You could run the code on their machine manually by using F8 and see if / where it doesn't do as it's supposed to.....and then post back with more info

Hi Michael,

The strange thing is that no error message is presented, it displays the message that indicates that the macro has worked and the request has been processed, however Shelley and dispatch do not recieve an email and it does not print either. Macros are enabled, the rest of the macro works great.

Not sure how relevant but I should mention that the CSRs use a joint outlook account via webmail.

Thanks for the help, I'll use F8 and work through with them next time they get a request.

Thanks

Kris