0

I am currently working on automating some data-related tasks that are regularly sent to me via email. Initially, I just want to insert these data into my master file and update it, which works perfectly fine. However, I have two questions regarding programming in Outlook:

  1. How can I wait for the security scan that my company performs on every new attachment by default? When I run the script to update the Excel file upon receiving the new email, it tells me that the file name is incorrect because the security scan is still being performed. The file is not saved in the defined path until the scan is completed.

  2. I have tried to identify the sender of the email not only by the subject title but also by their specific email address. However, I work for a large company, and the email addresses displayed in Outlook as Name.Surname@Company.com don't seem to work in the code. I have seen in the Direct window that Exchange addresses are being used. Is there any way to find out the exact Exchange address, as currently, I cannot differentiate between Name.Surname@Company.com and the address displayed in the Direct window?

Here is my code as text since I can't seem to upload screenshots at the moment. The code works even when the security scan is not conducted (I tested by sending myself the same attachment multiple times). In this case, the new source file is opened and my master file is updated as I specified.

Private Sub olItems_ItemAdd(ByVal item As Object)

    'Variablen dimensionieren
    Dim olMail As Outlook.MailItem
    Dim olAtt As Outlook.Attachment
    Dim Dateipfad As String

    'Prüfen, ob Item eine Mail ist
    If TypeName(item) = "MailItem" Then
        Set olMail = item

        'Prüfen, ob E-Mail verwendet werden soll
        If InStr(olMail.Subject, "Auswertung Pickleistung und Bedarfe") <> 0 Then

'And olMail.SenderEmailAddress = "patrick.staniczek@firmenname.com" Then

'Master Datei mit neuen Daten befüllen

'Schleife über alle Anhänge
  For Each olAtt In olMail.Attachments
                Dateipfad = 
  "Z:\Administration\Controlling\Controlling\MWZ\05_Lagerkennzahlen" & 
  olAtt.Filename

  'Datei speichern  
   olAtt.SaveAsFile Dateipfad

  'Daten aus Source in Master kopieren
   Call Auswertung_Pickleistung_und_Bedarfe(Dateipfad)
   Next olAtt

'Test
 Debug.Print olMail.Subject
 Debug.Print olMail.SenderEmailAddress
 Debug.Print olMail.Attachments.Count

End If

End If

End Sub

Thank you in advance for any helpful tips.

Best regards

After correction the script should run, only after the secruity scan is done by waiting a specified time amount.

Patrick Staniczek
  • 1
  • 1
  • You could use `Application.Wait` as shown [here](https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba), I'm assuming the processing of said data isn't needed the next second so you could add plenty of time to the wait for the security check to come through or have a loop with a `Application.Wait` wherein you check if the path exists yet see [this question about "if file exists"](https://stackoverflow.com/questions/16351249/vba-check-if-file-exists) – Notus_Panda Jun 23 '23 at 10:07
  • Thumbs up for "Here is my code as text since I can't seem to upload screenshots at the moment." - that's how the site likes it. You can't copy/paste from a screenshot. – Darren Bartrup-Cook Jun 23 '23 at 10:49
  • After saving the file you could run a loop checking if the file exists and only continue when it does. Maybe add an extra so it only loops so many times, or stops trying after a certain amount of time. [VBA check if file exists](https://stackoverflow.com/questions/16351249/vba-check-if-file-exists) – Darren Bartrup-Cook Jun 23 '23 at 10:55
  • Maybe [this](https://learn.microsoft.com/en-us/office/vba/outlook/concepts/address-book/map-a-display-name-to-an-e-mail-address) code by Sue Mosher might help with resolving the email address? – Darren Bartrup-Cook Jun 23 '23 at 11:00

0 Answers0