CODEWITHSUNDEEP
vbaoutlook
user2725402
user2725402

Reputation: 4629

Outlook VBA macro to create txt file

I am not very clued up with VBA and need some help with the following:

I want to create a txt file and save it in a specific location (overwriting the existing file with the same name).

The text file must countain the last word in the email subject, which will be an Account Number. For the purpose of this explanation I'm going to call the account number Variable1.

if the Email Subject = Account Recon - 10201314050019434586

then Variable1 = 10201314050019434586

The text file that must be created/replaced: C:\Users\tenba1\Documents\QlikView\Account Recons\Recon_Acct.txt

The text in the file must be: SET vAcct = 'Variable1';

So in this example the text in the file must be: SET vAcct = '10201314050019434586';

The macro must look at the last word in the subject (i.e. everything after the last space) - an account number is not always 20 digits.

Thanks in advance

Upvotes: 0

Views: 10761

Answers (1)

Iralution
Iralution

Reputation: 480

This should do the trick. It's a macro script which takes the Subject of the top opened email and writes the Accountnumber into Recon_Acct.txt. If there is no number, the text will be SET vAcct = ''. The File will be overwritten, every time you execute the script.

Sub writeSubjectToFile()
    Const FILEPATH = "C:\Users\tenba1\Documents\QlikView\Account Recons\Recon_Acct.txt"
    Dim objEmailItem As Object, strSubject
    Dim strSubject As String
    Dim strText As String

    Set objEmailItem = Application.ActiveInspector.CurrentItem

    strSubject = objEmailItem.subject
    strText = Trim(Right(strSubject, Len(strSubject) - InStr(1, strSubject, "-")))

    Open FILEPATH For Output As 1
    Print #1, "SET vAcct = '" & strText & "';"
    Close #1
End Sub

Upvotes: 6

Related Questions