help! e-mail text into Access 
Author Message
 help! e-mail text into Access

Who can write a script that does the following:

For each e-mail in Outlook folder X (which is second level down from
inbox) do
    on error move e-mail into Oulook folder "error"
    transform e-mail-text with a word macro (see below) and using the
sent-date of the e-mail
    paste result into Access query "webform"
    move e-mail into Outlook folder "done"

-----------
e-mail texts will look as follows:

****************************************************************************
***
Anrede:     w
First_Name: First
Last_Name:  Last
Titel:
Street:     Weinbergstr. x
PLZ:        67590
Ort:        London
Land:       D-
Phone:      0624799xxxx

Woher:      test

-------------

word macro to transform the e-mail text: Where it inserts "Datum" should be
the sent-date of the e-mail

Sub webform_neu()
    Selection.Paste
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "*"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    Selection.HomeKey Unit:=wdStory
    Selection.MoveRight Unit:=wdWord, Count:=3, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1

    ' in the line below the word Datum should be replaced by the sent-date
of the e-mail

    Selection.TypeText Text:="Datum" & vbTab & "webform" & vbTab & "ja" & _
        vbTab
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.TypeText Text:=vbTab
    Selection.MoveRight Unit:=wdWord, Count:=4, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.MoveRight Unit:=wdWord, Count:=4, Extend:=wdExtend
    Selection.TypeText Text:=vbTab
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.MoveRight Unit:=wdWord, Count:=2, Extend:=wdExtend
    Selection.TypeText Text:=vbTab
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    'Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.MoveRight Unit:=wdWord, Count:=2, Extend:=wdExtend
    Selection.TypeText Text:=vbTab
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.MoveRight Unit:=wdWord, Count:=2, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.TypeText Text:=vbTab
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "Land"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    Selection.Cut
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.HomeKey Unit:=wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.MoveLeft Unit:=wdWord, Count:=2, Extend:=wdExtend
    Selection.MoveLeft Unit:=wdCharacter, Count:=1
    Selection.Paste
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.MoveRight Unit:=wdWord, Count:=2, Extend:=wdExtend
    Selection.TypeText Text:=vbTab
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.MoveRight Unit:=wdWord, Count:=2, Extend:=wdExtend
    Selection.TypeText Text:=vbTab
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.MoveRight Unit:=wdWord, Count:=2, Extend:=wdExtend
    Selection.TypeText Text:=vbTab
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.MoveRight Unit:=wdWord, Count:=2, Extend:=wdExtend
    Selection.TypeText Text:=vbTab
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.MoveLeft Unit:=wdCharacter, Count:=1
    Selection.HomeKey Unit:=wdStory, Extend:=wdExtend
    Selection.ConvertToTable Separator:=wdSeparateByTabs, NumColumns:=13, _
        NumRows:=1, Format:=wdTableFormatNone, ApplyBorders:=True,
ApplyShading:= _
        True, ApplyFont:=True, ApplyColor:=True, ApplyHeadingRows:=True, _
        ApplyLastRow:=False, ApplyFirstColumn:=True, ApplyLastColumn:=False,
_
        AutoFit:=True, AutoFitBehavior:=wdAutoFitFixed
    Selection.Cut
End Sub



Mon, 09 Dec 2002 03:00:00 GMT  
 help! e-mail text into Access

Quote:

>Who can write a script that does the following:

Sorry but no one is going to do your coding for you.  

Quote:
>For each e-mail in Outlook folder X (which is second level down from
>inbox) do

See the CDO and/or Outlook links as mentioned in the Access Email FAQ
on my website.

Tony
----
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
   VolStar http://www.volstar.com Manage hundreds or
thousands of volunteers for special events.



Sat, 14 Dec 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. help! e-mail text into Access

2. Need an Access VBA script to create/mail an email

3. Email text file from Access

4. Sending plain text email via Access 97 and Outlook

5. Newbie Emailing text file into body of Email

6. Transfering Text from Access to Word for Mail Merge through Code

7. Newbie: How to access text of current mail

8. HOW: Converting TEXT mail forms from Outlook to Access

9. Transfer Text from Access to Word for Mail Merge by Code

10. !!! Help Sending mail via MS Access Event Help !!!!

11. Sending HTML and text emails...need help!

12. Problem emailing from VB/Access when notes is email server

 

 
Powered by phpBB® Forum Software