VbaProject: Changing email format

Status
Not open for further replies.

divan

Member
Outlook version
Outlook 2007
Email Account
POP3
Hi

I need a script that can do the following:

Original Email
Email Subject:

Movement Alarm 555
Email Body:
Unit: 0 Version:03.091
Continuous Movement
Back Office
Zone 3
2015/01/20 @ 07h15
___________________________________________
Altered email after script was processed:
Email Subject:

Movement Alarm 555
Unit: 0 Version:03.091
Continuous Movement
Back Office
Zone 3
2015/01/20 @ 07h15
Email Body:
“Phone Number”
___________________________________________
My Script does it half way, it lacks in the following areas:
1. When I add the item.body to item.subject it replaces the whole subject (I only want to add it)
2. When It whould replace the body with mu “Phone Number” it adds a lot of gibberish which I don’t want

Example of what my email outcome is:
Original Email
Email Subject:

Movement Alarm 555
Email Body:
Unit: 0 Version:03.091
Continuous Movement
Back Office
Zone 3
2015/01/20 @ 07h15
___________________________________________
Altered email after script was processed:
Email Subject:

Unit: 0 Version:03.091
Continuous Movement
Back Office
Zone 3
2015/01/20 @ 07h15
Email Body:
Date: Mon, 19 Jan 2015 08:42:28 +0200
Message-ID: <000001d033b3$1a2ec660$4e8c5320$@co.za>
MIME-Version: 1.0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AdAzsgvdJDRuShGcSiaP+wcwbJIN/QAAQvAw
Content-Language: en-za

“Phone Number”
___________________________________________

This is my script:
Sub ChangeSubjectForward(Item As Outlook.MailItem)
Item.Subject = Item.Body
Item.Save
Set myForward = Item.Forward
myForward.Recipients.Add "sms@fakeserver.com"
myForward.Subject = Item.Body
myForward.Body = "Phone Number"
myForward.Send

End Sub
___________________________________________
 

Forum Admin

Senior Member
1. When I add the item.body to item.subject it replaces the whole subject (I only want to add it)
concatenate the fields - myForward.subject = myForward.subject & myForward.body

Because the subject doesn't handles lines breaks well, you should replace them with commas or another character (I'm assuming the body is 5 lines as you've posted here.)

strSubject = replace(item.body, vbcrlf, ",")
myForward.subject = myForward.subject & strSubject

2. When It whould replace the body with mu “Phone Number” it adds a lot of gibberish which I don’t want
Where is the phone number coming from? If it's plain text, it should work just fine with the following code.
myForward.Body = "Phone Number"

what do you get with this:
myForward.htmlBody = "Phone Number"

or try this:
myForward.BodyFormat = olFormatplaintext
myForward.Body = "Phone Number"
 

divan

Member
Outlook version
Outlook 2007
Email Account
POP3
concatenate the fields - myForward.subject = myForward.subject & myForward.body

Because the subject doesn't handles lines breaks well, you should replace them with commas or another character (I'm assuming the body is 5 lines as you've posted here.)

strSubject = replace(item.body, vbcrlf, ",")
myForward.subject = myForward.subject & strSubject



Where is the phone number coming from? If it's plain text, it should work just fine with the following code.
myForward.Body = "Phone Number"

what do you get with this:
myForward.htmlBody = "Phone Number"

or try this:
myForward.BodyFormat = olFormatplaintext
myForward.Body = "Phone Number"

I have tried the code you have given , but can't get it to work.

Not really sure as to where I should put in the code:

Sub ChangeSubjectForward(Item As Outlook.MailItem)
Item.Subject = myForward.Subject & myForward.Body <---This line gives me an error
Item.Save

Set myForward = Item.Forward
myForward.Recipients.Add "fake.email@gmail.com"
strSubject = Replace(Item.Body, vbCrLf, ",")
myForward.Subject = myForward.Subject & strSubject
myForward.HTMLBody = "Phone number" <---This makes no difference
myForward.Send

End Sub
 

divan

Member
Outlook version
Outlook 2007
Email Account
POP3
I have played around with the script and found that this works almost 100 except for the concatenate function in the subject

Sub ChangeSubjectForward(Item As Outlook.MailItem)
Item.Subject = Item.Body
Item.Save
Set myforward = Item.Forward
myforward.Recipients.Add "fake.email@server.com"
strSubject = Replace(Item.Body, vbCrLf, ", ") <-----This is so cool, thanks!
myforward.Subject = strSubject
myforward.Body = "Phone Number"
myforward.Send
End Sub

If I where to change this:
myforward.Subject = strSubject
to this:
myforward.Subject = Item.Body & strSubject

Then there is no change in the email and only gets forwarded as it arrived
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
I have tried the code you have given , but can't get it to work.

Not really sure as to where I should put in the code:

Sub ChangeSubjectForward(Item As Outlook.MailItem)
Item.Subject = myForward.Subject & myForward.Body <---This line gives me an error
Item.Save

Set myForward = Item.Forward
The line errors because you are referencing myForward before you told Outlook what myForward is.




myForward.HTMLBody = "Phone number" <---This makes no difference
This should add the words "phone number" to the body. Nothing more, nothing less.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
strSubject = Replace(Item.Body, vbCrLf, ", ") <-----This is so cool, thanks!

<snip>

If I where to change this:
myforward.Subject = strSubject
to this:
myforward.Subject = Item.Body & strSubject

Then there is no change in the email and only gets forwarded as it arrived
The first line strips the line feeds from the body to make one long line. in your two versions of myforward.subject, the first one adds the body then adds it again, with the line feeds stripped.

if you want to combine the original subject and the body, you need to hook them together.
myforward.Subject = Item.Subject & "," & strSubject
 

divan

Member
Outlook version
Outlook 2007
Email Account
POP3
Hi Diane.

Thanks for all your help so far!
I finally got the script to work 100%....now I need to duplicate the script to have other recipients.

Example of how this is working now:
Our office (Site-1) has a CCTV system that sends out a email to Outlook that then gets forwarded to Linux box that SMS it out.
Now I need to add the CCTV system from home (Site-2) to send email to Outlook and also the forward it to the same Linux box.

What I want to accomplish:
Site-1 and 2 each have their own email address. Outlook determines the source of email (By using outlook Rules) and then tell it to run a scrip which is my Project1 (VBAProject.OTM)

But now I have two scripts each the same except for :
Site 1 = myforward.Body = "Phone Number-1"
Site 2 = myforward.Body = "Phone Number-2"

My rules does not seem to work when I have duplicated the script.
When I select my script to run in the rule it does show both of Project1.ChangeSubjectForward
bud does not do anything doesn't matter which one I choose.

The scrip only works if there is only one copy and one rule attached to the one scrip.

Please help!

Thanks in advance

Divan

This is what I have in VBA:
_____________________________________________
Sub ChangeSubjectForward(Item As Outlook.MailItem)
strSubject = Replace(Item.Body, vbCrLf, ", ")
Item.Subject = Item.Subject & " " & strSubject
Item.Body = "Phone number-1"
Item.Save

Set myforward = Item.Forward
myforward.Recipients.Add "Linux_box@domain.com"
myforward.Subject = "Alarm Detection" & " " & Item.Subject
myforward.Body = "Phone Number-1"
myforward.Send

End Sub
_____________________________________________

Sub ChangeSubjectForward(Item As Outlook.MailItem)
strSubject = Replace(Item.Body, vbCrLf, ", ")
Item.Subject = Item.Subject & " " & strSubject
Item.Body = "Phone number-2"
Item.Save

Set myforward = Item.Forward
myforward.Recipients.Add "Linux_box@domain.com"
myforward.Subject = "Alarm Detection" & " " & Item.Subject
myforward.Body = "Phone Number-2"
myforward.Send

End Sub
_____________________________________________
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
There are two ways to do this - duplicate the script with a new name or use 3 scripts - one to set the phone in a global variable and a shared one to do the work. The rule would call the one that set the # and that script would call the shared script.

Because the macro is just a few lines, I'd probably use the full macro and change the name:
Sub Site1Forward(Item As Outlook.MailItem)
Sub Site2Forward(Item As Outlook.MailItem)

if you needed to change more things or it was a long macro, you'd do something like this - but since it's about the same amount of text either way, it doesn't really matter.

===========================
dim strPhone as string

Sub Site1Forward(Item As Outlook.MailItem)
strphone = "site1 phone"
ChangeSubjectForward
end sub

Sub Site2Forward(Item As Outlook.MailItem)
strphone = "site2 phone"
ChangeSubjectForward
end sub

Sub ChangeSubjectForward()
strSubject = Replace(Item.Body, vbCrLf, ", ")
Item.Subject = Item.Subject & " " & strSubject
Item.Body = strphone
Item.Save

Set myforward = Item.Forward
myforward.Recipients.Add "Linux_box@domain.com"
myforward.Subject = "Alarm Detection" & " " & Item.Subject
myforward.Body = strphone
myforward.Send

End Sub
=============================
 

divan

Member
Outlook version
Outlook 2007
Email Account
POP3
It makes sense and is a very good idea to have a shared script.
Just tried it now...
I went into rules and created the following rules:
------------------------------
Apply this rules after message arrives
from site-1@domain.com
and on this machine only
run Project1.Site1Forward
------------------------------
------------------------------
Apply this rules after message arrives
from site-2@domain.com
and on this machine only
run Project1.Site2Forward
------------------------------


If I receive a mail from either site I get an Run-time error '424'
Object Required

When I select either rule and try and run them on folders where email it does nothing

Any Ideas?
 

divan

Member
Outlook version
Outlook 2007
Email Account
POP3
There are two ways to do this - duplicate the script with a new name or use 3 scripts - one to set the phone in a global variable and a shared one to do the work. The rule would call the one that set the # and that script would call the shared script.

Because the macro is just a few lines, I'd probably use the full macro and change the name:
Sub Site1Forward(Item As Outlook.MailItem)
Sub Site2Forward(Item As Outlook.MailItem)

if you needed to change more things or it was a long macro, you'd do something like this - but since it's about the same amount of text either way, it doesn't really matter.

===========================
dim strPhone as string

Sub Site1Forward(Item As Outlook.MailItem)
strphone = "site1 phone"
ChangeSubjectForward
end sub

Sub Site2Forward(Item As Outlook.MailItem)
strphone = "site2 phone"
ChangeSubjectForward
end sub

Sub ChangeSubjectForward()
strSubject = Replace(Item.Body, vbCrLf, ", ")
Item.Subject = Item.Subject & " " & strSubject
Item.Body = strphone
Item.Save

Set myforward = Item.Forward
myforward.Recipients.Add "Linux_box@domain.com"
myforward.Subject = "Alarm Detection" & " " & Item.Subject
myforward.Body = strphone
myforward.Send

End Sub
=============================
The run-time error refers to
strSubject = Replace(Item.Body, vbCrLf, ", ")
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Let me see what i forgot.... ah... to set the item.


Code:
Dim strPhone As String

Sub Site1Forward(Item As Outlook.MailItem)
strPhone = "site1 phone"
ChangeSubjectForward Item
End Sub

Sub Site2Forward(Item As Outlook.MailItem)
strPhone = "site2 phone"
ChangeSubjectForward Item
End Sub

Sub ChangeSubjectForward(Item As Outlook.MailItem)
strSubject = Replace(Item.Body, vbCrLf, ", ")
Item.Subject = Item.Subject & " " & strSubject
Item.Body = strPhone
Item.Save

Set myforward = Item.Forward
myforward.Recipients.Add "Linux_box@domain.com"
myforward.Subject = "Alarm Detection" & " " & Item.Subject
myforward.Body = strPhone
myforward.Send

End Sub
 

divan

Member
Outlook version
Outlook 2007
Email Account
POP3
Thanks so much Diane!
Works like a charm!

What would the max sites be that I can add to the script?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
AFAIK, the only limitation would be in the number of rules allowed in Outlook - with POP3, it's virtually unlimited, or as many as you can manage without going nuts. The bigger problem would be multiple messages coming in at the same time, trying to be processed by the same script. If that happens often, it might help to use individual scripts for each number, not the shared script, but its also possible that the rules will just not process the messages.
 

divan

Member
Outlook version
Outlook 2007
Email Account
POP3
Thanks for all your help so far Diane
This would hopefully be my last question to you:
I altered the script to give each Site a username and password which must be unique. The way I assigned it was the same as for the phone number but I think I slipped up somewhere.
Somehow it does not enter the given strUser = "UsernameX Password " info in my script.
What am I missing?

This is my whole VBA Script:

Dim strPhone As String
________________________________________
Sub Site001Forward(Item As Outlook.MailItem)
strPhone = "Site001 Phone"
strUser = "Username1 Password "
ChangeSubjectForward Item
End Sub
________________________________________
Sub Site002Forward(Item As Outlook.MailItem)
strPhone = "Site002 Phone"
strUser = "Username2 Password "
ChangeSubjectForward Item
End Sub
________________________________________
Sub Site003Forward(Item As Outlook.MailItem)
strPhone = "Site003 Phone"
strUser = "Username3 Password "
ChangeSubjectForward Item
End Sub
________________________________________
Sub ChangeSubjectForward(Item As Outlook.MailItem)
strSubject = Replace(Item.Body, vbCrLf, ", ")
Item.Subject = Item.Subject & " " & strSubject
Item.Body = strPhone
Item.Save
Set myforward = Item.Forward
myforward.Recipients.Add "Linux_box@domain.com"
myforward.Subject = strUser & Item.Subject
myforward.Body = strPhone
myforward.Send
End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Variables that are shared/reused need to be dimmed outside of the macros - like strPhone is. Add a line for strUser and it should work. :)
Dim strUser As String
 
Status
Not open for further replies.
Top