The VbaProject file in outlook

[Mike375]

Searching around would indicate that if I make macros in Outlook and then copy the VbaProject file to someone else's Outlook then they have the macros installed. The proviso being that they do not have any existing macros as they would be wiped out.

Is there anything else that copying the VbaProject file could wipe out?

Or is there any other way where I could copy to the other persons computer.

If I had a laptop with me then of course I could copy the code from mine and directly make macros on the other persons computer but I would prefer to be able to either email or have the file on a thumb drive.

 

[darbid]

I recently went down your path and came to the same conclusion. ie It will wipe current macros - I also concluded that people could then respectively wipe your macro or play with it.

As far as I can work out there is only two ways to distribute your Outlook macro to people - you have the first and the second is to make an Outlook Add-in.

It sounds hard but I managed it. I did however only get it right for VB6 - for me all the extras , dependancies, .net stuff etc etc of VS2005/VS2008 confused the hell out of me and I could not get it right.

in this thread I pretty much spelt it out for VB6

http://www.access-programmers.co.uk/forums/showthread.php?t=174119

 

[Mike375]

I recently went down your path and came to the same conclusion. ie It will wipe current macros - I also concluded that people could then respectively wipe your macro or play with it.

I have not checked yet but I have the feeling that you can't password protect them like you can in Excel.

Although for the way I do business I not too worried about those aspects, mainly ease of putting then there.

In my canvassing for business so far I only rarely come across a small business that has macro in either Outlook, Word or Excel. I have encountered a few advanced Excel users but they were limited to cells. As to Outlook I think this forum is similar as 99.99% of Outlook postings seem to relate to the automating email sending and the code for Access or Excel.

Will look at the link directly.

Edit: Yo might have seen this link.

http://www.outlookcode.com/article.aspx?id=28

 

[Mike375]

http://www.access-programmers.co.uk/forums/showthread.php?t=174119

Looks like you had a few sessions on the keyboard while the sun was rising.

As to Outlook back to Access what I am doing so far is using Allen Browne's ListFiles to a table. I currenly have the saved attachment and also Word doc for grapping the body naming the following way:

2009-07-22 20-11-13 mike375@optusnet.com.au Mike McGuire Subject was Test123 0LetterMike


The last part is the original file name. Once the file names are in the table then I can extract the email address and then link to the customer or whatever and ditto for the Word doc with the body.

If you have any tips I would appreciate them as there does not seem to many on the forum that have pushed down this road.

 

[darbid]

Edit: Yo might have seen this link.
http://www.outlookcode.com/article.aspx?id=28

I have learnt a lot of my Outlook stuff from that forum. Sue answers your questions as I suppose she wants you to buy her book.

Looks like you had a few sessions on the keyboard while the sun was rising.

I wrote my first VBA code one year ago so it was also after a few beers as well.

The last part is the original file name. Once the file names are in the table then I can extract the email address and then link to the customer or whatever and ditto for the Word doc with the body.

So what are you looking for here? I am assuming the user selects an email then clicks a button and you want to save the email addresses, attachments and body of the email in a database? Is this right?

 

[Mike375]

So what are you looking for here? I am assuming the user selects an email then clicks a button and you want to save the email addresses, attachments and body of the email in a database? Is this right?

I have the save part done for both attachment and body and with that naming routine. I have the attachment save done where it will do all the existing emails in one go or selected emails, as in those highlighted.

Next thing is to get the file name into a table and then Access can open it and of course link to the customers record by extracting the email into another field and I will be able to do that OK.

But then there is extracting a specified part part of the email body. Thinking mainly here of pulling the email address from bounced back emails as this will have some relevance where the business does an email campaign. This is probably my main remaining hurdle and of course the general issue of putting it on other people's computers and at the moment I will probably go with the VBAProject file.

Agree, Sue knows her Outlook.

 

[darbid]

But then there is extracting a specified part part of the email body.

If I understand you correctly you have the body saved to a word doc - if so you have very powerful find and replace and extraction methods.

Thinking mainly here of pulling the email address from bounced back emails as this will have some relevance where the business does an email campaign.

I would have thought that you should check for this before saving it in the DB. Surely one of the properties of the MailItem would indicate that it is a bounced back email.

This is probably my main remaining hurdle and of course the general issue of putting it on other people's computers and at the moment I will probably go with the VBAProject file.

That is certainly the easiest way.

 

[Mike375]

If I understand you correctly you have the body saved to a word doc - if so you have very powerful find and replace and extraction methods.

Word is opened the email body goes in the Word doc saved as in the above name system and so there is no replace system as their is a Word oc for each email. The emails to be done are selected ones while in Outlook.

I would have thought that you should check for this before saving it in the DB. Surely one of the properties of the MailItem would indicate that it is a bounced back email.

I have not tried anything yet but bounce backs on a couple of dummy emails I just sent had for the subject

Mail delivery failed: returning message to sender

So I could detect them although at the moment I would only detect then because they have been saved and I could have them delete from the folder after the save. This is a general area where I am behind at the moment. That is, I will "filter" from what is saved as opposed to a system that by passes email or file attachment types.

The following is in the body

The following address(es) failed:

mike375@opus.com.au

If I can extract that email address then it will easy enough to match it to the customer record that has the dud address.

 

[Mike375]

PS. I think the VBAProject method will be OK for me because anyone who buys what I do in Outlook will either have me putting on it from a thumb disk or emailing the file while they are on the phone.

 

[darbid]

Thinking out aloud;

These emails are only received emails, the email address will not be in your customers list -

Then I start getting a little weak. But in the body of this email there is an email address that is in your customers list.

But maybe email systems have some standard "things" in these reply emails. Mine always come from system administrator and have the words failed in them or unknown.

 

[Mike375]

The customers email is in the body of the bounced back email.

Thus I need to extract that from the body of the email and then tghey can be linked with the customers who have the dud email addresses.

As a side note I have got the email address or body of the email etc into an Access field.

I placed the code to open the DB under what opens Word

wdDoc.Content = MyOlMail.Body
[Forms]![GoToExcel]![Text1] = MyOlMail.SenderEmailAddress

But the problem is it only works once, that is, Outlook has to be closed and reopened for it to work again. However, I don't really need it to insert difrect to Access and just doing it so I know I can do it. Also it would not know which record to go to

 

[Mike375]

This what comes back in mine

This message was created automatically by mail delivery software.

A message that you sent could not be delivered to one or more of its
recipients. This is a permanent error. The following address(es) failed:

mike375@opus.com.au
Unknown user

------ This first part of the message. ------

Message-ID: <000601ca0b7a$66c172f0$e0186e7a@mike>
From: "Mike McGuire" <mike375@optusnet.com.au>
To: <Mike375@opus.com.au>
Subject: Test
Date: Thu, 23 Jul 2009 19:46:03 +1000

This is a multi-part message in MIME format.

------=_NextPart_000_0003_01CA0BCE.364A54D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


------=_NextPart_000_0003_01CA0BCE.364A54D0
Content-Type: text/html;

 

[darbid]

yeh it seems this will be different here is mine

Your message did not reach some or all of the intended recipients.

Subject:
Sent: 23/07/2009 12:38 PM

The following recipient(s) cannot be reached:

'dafdasdf@gmx.de' on 23/07/2009 12:38 PM
550 5.1.1 <dafdasdf@gmx.de>... User is unknown {mp022}

 

[Mike375]

yeh it seems this will be different here is mine

Your message did not reach some or all of the intended recipients.

Subject:
Sent: 23/07/2009 12:38 PM

The following recipient(s) cannot be reached:

'dafdasdf@gmx.de' on 23/07/2009 12:38 PM
550 5.1.1 <dafdasdf@gmx.de>... User is unknown {mp022}

Once that was in Word and say Word content went to an Access memo field then you could get Mid and Instrt to get the < and >. But in mine I have < and > against the sender email and the dud email comes later so two lots of < and >. I am guessing that <dud email address> will always be there.

By the way, what in your Subject for a bounced email. I have for Outlook Express and Outlook and if the dud email is .com.au

Mail delivery failed: returning message to sender

But if the dud is just .com then I get

Returned mail: see transcript for details

I have just been searching based on exracted text but so far I am finding getting the whole body into Excel and then I assume extracting from Excel.

Sounds like some late nights coming up

 

[Mike375]

I found this

http://www.codeforexcelandoutlook.com/blog/2009/02/bounced-email-list-maker/

Does your bounced email also have your email address as mine do.

If that is standard.....and in my case my email address is preceded by From: then maybe something can be done since the From: email will all be the same.

 

[Mike375]

I have about given up on extrating the email address from bounce back emails. The variation is quite large including some where the email address is not in the body of the email. In some cases there is a little attachment in the bounce back notifying email and when opened shows the original email and with the dud email adress following To:

In addition not all cases have the <> around the email address in the body of the email.

The bounce back email can also have other stuff between <> such as

Message-ID: <000601ca0b7a$66c172f0$e0186e7a@mike>

and in other the dud email can be in the body 3 or 4 times. Look at this one, it is there 5 times

The original message was received at Fri, 24 Jul 2009 01:07:50 +1000
from 114.73.165.212.optusnet.com.au [114.73.165.212] (may be forged)

----- The following addresses had permanent fatal errors -----
<dafdasdf@gmx.de>
(reason: 550 5.1.1 <dafdasdf@gmx.de>... User is unknown {mx023})

----- Transcript of session follows -----
... while talking to mx0.gmx.net.:
>>> RCPT To:<dafdasdf@gmx.de>
<<< 550 5.1.1 <dafdasdf@gmx.de>... User is unknown {mx023}
550 5.1.1 <dafdasdf@gmx.de>... User unknown

The thought I am having as a last attempt is to extract any <xxxx> and then Replace it with " " and extract <> again etc. A link up could establish which is the senders email. The other stuff with <> that is not the dud email address doe snot seem to have the @ in it.

 

[darbid]

I agree it is going to be hard. I cannot seem to get to it anymore but with old outlooks I could display the header of the email which was the email in text form. This form would have to follow some internet standard I bet. Now in there would have to be an error code.

For example error 404 for internet pages. If you are just looking at the page then it might show many things from your web browsers standard page to a page built by the company that you are trying to surf to. but if you look at the response given to your web browser there will be somewhere in there 404. I am wondering if there is also the same concept with emails.

By the way as you are using word for the body of your email that means you can use regular expressions to pull out all the email addresses. I have not done it with emails but here is an example i just found by searching -

[\w\-][\w\-\.]+@[\w\-][\w\-\.]+[a-zA-Z]{1,4}

• [\w\-] matches any "word" character (letter, number, or underscore), or a hyphen
• [\w\-\.]+ matches (any word character or hyphen or period) one or more times
• @ matches a literal '@'
• [\w\-] matches a word character or hyphen
• [\w\-\.]+ matches one or more word characters, hyphens, and/or periods
• [a-zA-Z]{1,4} matches 1, 2, 3, or 4 lowercase or uppercase letters

I am assuming that you know this, but if not you can manually test this by putting your expression into the find of word.

 

[Mike375]

I had not seen that before.

I was just in Access

Exp3: Mid([xyz],InStr([xyz],"<")+1,(InStr([xyz],">")-InStr([xyz],"<"))-1)

And same sort of thing in Outlook

MyOlMail.Body = Mid(MyOlMail.Body, InStr(MyOlMail.Body, "<") + 1, (InStr(MyOlMail.Body, ">") - InStr(MyOlMail.Body, "<")) - 1)

wdDoc.Content = MyOlMail.Body

The last one results in

HYPERLINK "mailto:mike272@optusnet.com.au"mike272@optusnet.com.au

being put in Word. But of course the problem is there is more than one email address there.

I have just put a post on Outlookcode.com.

 

[darbid]

Here is my code I use to get all of my "strings" out of a word doc. Play with your email regular expression and change .text to whatever works for you.

'going backward to get the application
Set myWordApplication = myDoc.Application

'just making sure it is hidden
myWordApplication.WindowState = wdWindowStateMinimize

'make sure our search starts at the beginning
Set myRange = myDoc.Range(Start:=myDoc.Range.Start, End:=myDoc.Range.End)

'First get all the full AKZs  this is the string I want to get out
myWordApplication.Selection.Find.ClearFormatting
With myWordApplication.Selection.Find
    .Text = "[0-9]{4}[A-Z][0-9]{5}"  'put your choice of expression here
    .Replacement.Text = ""
    .Forward = True
    .wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchByte = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchFuzzy = False
    .MatchWildcards = True
End With

Do While myWordApplication.Selection.Find.Execute
    Debug.Print myWordApplication.Selection
    If InStr(str_AKZ_All, myWordApplication.Selection) = 0 Then
        str_AKZ_All = str_AKZ_All & ";" & myWordApplication.Selection
    End If
Loop

 

[Mike375]

I will have a play with that, thanks.

I might also be onto something else. So far each of the bounce backs I have generated have an attachment. Opening the attachment amounts to opening the original email and thus it has To: which is the dud email address.

MyOlMail.To gets who an email was sent to. I just stumbled across that. I was trying MyOlMail.Recipients which came from the help in VBA (intellesense is it called) and just for a joke I tried To and it gets the email address of who it was sent to. And To is in the intellesence because I went and checked.

I can save the attachments to the bounce backs but don't what to do with them so as to get to the To: Actually there are two attachments and one is a text file. Here is a copy of one I just did. The emails in blue are the dud I used.

Reporting-MTA: dns; mail03.syd.optusnet.com.au
Received-From-MTA: DNS; 114.73.132.39.optusnet.com.au
Arrival-Date: Fri, 24 Jul 2009 18:00:24 +1000
Final-Recipient: RFC822; mike372@opti.com
Action: failed
Status: 5.1.1
Remote-MTA: DNS; mx5.business.mindspring.com
Diagnostic-Code: SMTP; 550 mike372@opti.com...User unknown
Last-Attempt-Date: Fri, 24 Jul 2009 18:00:27 +1000