问:
How to implement chat using jQuery, PHP, and MySQL?
I have made a chat script using php, mysql and jquery. It uses json to get data from the server. It makes fixed interval requests to the server with the lastly fetched message id to get new messages from the server. But when multiple users will be chatting then thousands and crores of requests will be made to the server within an hour and the hosting people will block it for sure.

Th gmail chat uses socket I think. Because it does not sends fixed interval requests for sure. Could any one of you please give me some sample code or some direction to solve this issue.

Please I need help desperately.

Many thanks in advance. My respect and regards for all.

答:
You may find it useful to see an example of 'comet' technology in action using Prototype's comet daemon and a jetty webserver. The example code for within the jetty download has an example application for chat.

I recently installed jetty myself so you might find a log of my installation commands useful:

Getting started trying to run a comet service

Download Maven from http://maven.apache.org/

Install Maven using http://maven.apache.org/download.html#Installation
I did the following commands
Extracted to /home/sdwyer/apache-maven-2.0.9

```<code>> sdwyer@pluto:~/apache-maven-2.0.9$ export M2_HOME=/home/sdwyer/apache-maven-2.0.9
> sdwyer@pluto:~/apache-maven-2.0.9$ export M2=$M2_HOME/bin
> sdwyer@pluto:~/apache-maven-2.0.9$ export PATH=$M2:$PATH.
> sdwyer@pluto:~/apache-maven-2.0.9$ mvn --version
-bash: /home/sdwyer/apache-maven-2.0.9/bin/mvn: Permission denied

> sdwyer@pluto:~/apache-maven-2.0.9$ cd bin
> sdwyer@pluto:~/apache-maven-2.0.9/bin$ ls
m2 m2.bat m2.conf mvn mvn.bat mvnDebug mvnDebug.bat

> sdwyer@pluto:~/apache-maven-2.0.9/bin$ chmod +x mvn

> sdwyer@pluto:~/apache-maven-2.0.9/bin$ mvn –version

Maven version: 2.0.9
Java version: 1.5.0_08
OS name: “linux” version: “2.6.18-4-686″ arch: “i386″ Family: “unix”
sdwyer@pluto:~/apache-maven-2.0.9/bin$
</code>```

Download the jetty server from http://www.mortbay.org/jetty/
Extract to /home/sdwyer/jetty-6.1.3

```<code>> sdwyer@pluto:~$ cd jetty-6.1.3//examples/cometd-demo

> mvn jetty:run
</code>```

A whole stack of downloads run

Once it’s completed open a browser and point it to:
http://localhost:8080 and test the demos.

The code for the example demos can be found in the directory:

```<code>jetty-6.1.3/examples/cometd-demo/src/main/webapp/examples
</code>```

ContentLicense: CC BY-SA 2.5

LastEditorDisplayName: ryeguy

Body:
You may find it useful to see an example of 'comet' technology in action using Prototype's comet daemon and a jetty webserver. The example code for within the jetty download has an example application for chat.

I recently installed jetty myself so you might find a log of my installation commands useful:

Getting started trying to run a comet service

Download Maven from http://maven.apache.org/

Install Maven using http://maven.apache.org/download.html#Installation
I did the following commands
Extracted to /home/sdwyer/apache-maven-2.0.9

> sdwyer@pluto:~/apache-maven-2.0.9$ export M2_HOME=/home/sdwyer/apache-maven-2.0.9
> sdwyer@pluto:~/apache-maven-2.0.9$ export M2=$M2_HOME/bin
> sdwyer@pluto:~/apache-maven-2.0.9$ export PATH=$M2:$PATH.
> sdwyer@pluto:~/apache-maven-2.0.9$ mvn --version
-bash: /home/sdwyer/apache-maven-2.0.9/bin/mvn: Permission denied

> sdwyer@pluto:~/apache-maven-2.0.9$ cd bin
> sdwyer@pluto:~/apache-maven-2.0.9/bin$ ls
m2 m2.bat m2.conf mvn mvn.bat mvnDebug mvnDebug.bat

> sdwyer@pluto:~/apache-maven-2.0.9/bin$ chmod +x mvn

> sdwyer@pluto:~/apache-maven-2.0.9/bin$ mvn –version

Maven version: 2.0.9
Java version: 1.5.0_08
OS name: “linux” version: “2.6.18-4-686″ arch: “i386″ Family: “unix”
sdwyer@pluto:~/apache-maven-2.0.9/bin$

Download the jetty server from http://www.mortbay.org/jetty/
Extract to /home/sdwyer/jetty-6.1.3

> sdwyer@pluto:~$ cd jetty-6.1.3//examples/cometd-demo

> mvn jetty:run

A whole stack of downloads run

Once it’s completed open a browser and point it to:
http://localhost:8080 and test the demos.

The code for the example demos can be found in the directory:

jetty-6.1.3/examples/cometd-demo/src/main/webapp/examples

OwnerDisplayName: Steerpike


来源: stackexchange

问:
Software for browsing classes in a C/C++ project
I have seen that some developers have a graphical representation of all their classes in an image file which comes with their project. How can I myself create these graphics?

Basically they show what classes exist in a file and how these files relate to each other.

Thanks

答: Eclipse has the ability to generate class diagrams and export them as images.</p>

ContentLicense: CC BY-SA 3.0


问:
ef core navigation property fails to load
My application is Ef core 2.2 , Lazy loading not enabled.

At MapAddress method address object's Country object is null, although i have

Include(a => a.Address).ThenInclude(a => a.Country)

to load country eagerly.

```<code>   var agentWalletResponse = (from wd in dbContext.WalletDetail.Where(w => w.WalletDetailId == agentWalletDetailId)
                                    join c in dbContext.CorporateInfo.Include(a => a.Address).ThenInclude(a => a.Country).Where(d => !d.IsDeleted) on wd.WalletSubscriptionId equals c.OwnerUserId
                                    select new AgentWalletResponse()
                                    {

                                        Address = MapAddress(c.Address),
                                        Balance = wd.AvailableBalance,
                                        CreatedOn = wd.CreatedOn
                                    }).FirstOrDefault();
</code>```

Map address

```<code> protected AddressModel MapAddress(Address address)
    {
        if (address == null)
            return null;

        return new AddressModel
        {
            AddressId = address.AddressId,
            AddressLine = address.AddressLine1,
            City = address.City,
            Country = address.Country?.Name,
            Province = address.Province,
            Division = address.Division,
            PhoneNumber = address.PhoneNumber,
            PostalCode = address.PostalCode
        };
    }
</code>```

答:
I Achieved this introducing another intermediate method which accept CorporateInfo instead of the address.

```<code>var agentWalletResponse = (from wd in dbContext.WalletDetail
                                    join ws in dbContext.WalletSubscription on wd.WalletSubscriptionId equals ws.WalletSubscriptionId
                                    join ci in dbContext.CorporateInfo.Include(a => a.Address).ThenInclude(a => a.Country).Where(d => !d.IsDeleted) on wd.WalletSubscriptionId equals ci.OwnerUserId
                                    join wc in dbContext.Currency on wd.CurrencyId equals wc.CurrencyId
                                    join ac in dbContext.AgentCommission on ws.WalletSubscriptionId equals ac.WalletSubscriptionId into y
                                    from agentComms in y.DefaultIfEmpty()
                                    join kf in dbContext.KokuFee on ws.WalletSubscriptionId equals kf.WalletSubscriptionId into z
                                    from kf_all in z.DefaultIfEmpty()
                                    where ws.WalletType.Equals(WalletType.Agent) && !kf_all.IsDeleted && wd.WalletDetailId.Equals(agentWalletDetailId)
                                    && !agentComms.IsDeleted
                                    select new AgentWalletResponse
                                    {
                                        Alias = ws.Alias,
                                        Address = MapAddress(ci),
                                        AgentSubscriptionId = ws.WalletSubscriptionId,
                                        AgentWalletDetailId = wd.WalletDetailId,
                                        SubscriptionNo = ws.SubscriptionNo,
                                        Balance = wd.AvailableBalance,
                                        CurrencyCode = wc.CurrencyCode,
                                        Commission = agentComms == null ? 0 : agentComms.Commission,
                                        TopupFee = kf_all == null ? 0 : kf_all.AbsoluteFee,
                                        CreatedOn = wd.CreatedOn
                                    }).FirstOrDefault();
</code>```

```<code> //When use directly ef core fails to load the country of the address.
    protected AddressModel MapAddress(CorporateInfo corporateInfo)
    {
        return MapAddress(corporateInfo.Address);
    }
</code>```

What i think is when i request C.Address inside projection, it just ignore my ThenInclude command at query generation.

ContentLicense: CC BY-SA 4.0

Body:
My application is Ef core 2.2 , Lazy loading not enabled.

At MapAddress method address object's Country object is null, although i have

Include(a => a.Address).ThenInclude(a => a.Country)

to load country eagerly.

   var agentWalletResponse = (from wd in dbContext.WalletDetail.Where(w => w.WalletDetailId == agentWalletDetailId)
                                    join c in dbContext.CorporateInfo.Include(a => a.Address).ThenInclude(a => a.Country).Where(d => !d.IsDeleted) on wd.WalletSubscriptionId equals c.OwnerUserId
                                    select new AgentWalletResponse()
                                    {

                                        Address = MapAddress(c.Address),
                                        Balance = wd.AvailableBalance,
                                        CreatedOn = wd.CreatedOn
                                    }).FirstOrDefault();

Map address

 protected AddressModel MapAddress(Address address)
    {
        if (address == null)
            return null;

        return new AddressModel
        {
            AddressId = address.AddressId,
            AddressLine = address.AddressLine1,
            City = address.City,
            Country = address.Country?.Name,
            Province = address.Province,
            Division = address.Division,
            PhoneNumber = address.PhoneNumber,
            PostalCode = address.PostalCode
        };
    }

Body:
I Achieved this introducing another intermediate method which accept CorporateInfo instead of the address.

var agentWalletResponse = (from wd in dbContext.WalletDetail
                                    join ws in dbContext.WalletSubscription on wd.WalletSubscriptionId equals ws.WalletSubscriptionId
                                    join ci in dbContext.CorporateInfo.Include(a => a.Address).ThenInclude(a => a.Country).Where(d => !d.IsDeleted) on wd.WalletSubscriptionId equals ci.OwnerUserId
                                    join wc in dbContext.Currency on wd.CurrencyId equals wc.CurrencyId
                                    join ac in dbContext.AgentCommission on ws.WalletSubscriptionId equals ac.WalletSubscriptionId into y
                                    from agentComms in y.DefaultIfEmpty()
                                    join kf in dbContext.KokuFee on ws.WalletSubscriptionId equals kf.WalletSubscriptionId into z
                                    from kf_all in z.DefaultIfEmpty()
                                    where ws.WalletType.Equals(WalletType.Agent) && !kf_all.IsDeleted && wd.WalletDetailId.Equals(agentWalletDetailId)
                                    && !agentComms.IsDeleted
                                    select new AgentWalletResponse
                                    {
                                        Alias = ws.Alias,
                                        Address = MapAddress(ci),
                                        AgentSubscriptionId = ws.WalletSubscriptionId,
                                        AgentWalletDetailId = wd.WalletDetailId,
                                        SubscriptionNo = ws.SubscriptionNo,
                                        Balance = wd.AvailableBalance,
                                        CurrencyCode = wc.CurrencyCode,
                                        Commission = agentComms == null ? 0 : agentComms.Commission,
                                        TopupFee = kf_all == null ? 0 : kf_all.AbsoluteFee,
                                        CreatedOn = wd.CreatedOn
                                    }).FirstOrDefault();

 //When use directly ef core fails to load the country of the address.
    protected AddressModel MapAddress(CorporateInfo corporateInfo)
    {
        return MapAddress(corporateInfo.Address);
    }

What i think is when i request C.Address inside projection, it just ignore my ThenInclude command at query generation.


问:
Wordpress API in Angular 8. ERROR TypeError: "_co.item is undefined"
Here's my situation: I'm working with Wordpress API in a XAMMP local server as backend with Angular 8 frontend.
When I request all the posts, it works, but when I request about only one post, to show it into the component, I got this error:

ERROR TypeError: "_co.item is undefined"

View_PostsDetailComponent_0 PostsDetailComponent.html:5

Here is PostDetailComponent.html

```<code><app-private-nav>
    <div class="page-wrapper" id="main">
        <div fxLayout="row wrap" fxLayoutGap="16px" >
                <h1 [innerHTML]="item.title.rendered"></h1>
                <div [innerHTML]="item.content.rendered"></div>
        </div>
    </div>
</app-private-nav>
</code>```

I´m not using a model, but it suposed to work anyway.

```<code>import { Component, OnInit, Input } from '@angular/core';
import { PostsService } from './../posts.service';
import { ActivatedRoute, Router } from '@angular/router';

@Component({
  selector: 'app-posts-detail',
  templateUrl: './posts-detail.component.html',
  styleUrls: ['./posts-detail.component.scss']
})

export class PostsDetailComponent implements OnInit {
  item: any;
  id: number;

  constructor(private route: ActivatedRoute, public dataService: PostsService) { }
  ngOnInit() {

    this.route.url.subscribe(u => {
      this.id = this.route.snapshot.params.id;
    });

    this.dataService.getPostByID(this.id).subscribe((data: any) => {
      this.item = data;
    });
  }
</code>```

I'm using this url for the Wordpress API

wordpress/wp-json/wp/v2/posts/[ID]?_embed

And here is the PostService:

```<code>export class PostsService {
     item: any;
  constructor(private http: HttpClient) { }

  getPostByID(id: number): any {
    if (this.item) {
      return of(this.item);
    } else {
      return this.http.get(ENDPOINT_URL + 'wp/v2/posts' + `/${id}` + '?_embed').pipe(map(this.processPostDataID, this));
    }
    }

  processPostDataID(data: any) {
    this.item = data;

     return this.item;
      }
}
</code>```

Thanks a lot.

答:
The item isn't set to anything when the HTML first loads - it takes a small amount of time for the observable to return the item.

You can wrap the item calls with *ngIf. This will prevent "item.title.rendered" trying to access an undefined object.

```<code><app-private-nav>
  <div class="page-wrapper" id="main">
    <div *ngIf="item" fxLayout="row wrap" fxLayoutGap="16px" >
      <h1 [innerHTML]="item.title.rendered"></h1>
      <div [innerHTML]="item.content.rendered"></div>
    </div>
  </div>
</app-private-nav>
</code>```

You can also use optional chaining to handle individual calls. Optional chaining makes expressions operating on undefined or null objects safe.

```<code><app-private-nav>
  <div class="page-wrapper" id="main">
    <div fxLayout="row wrap" fxLayoutGap="16px" >
      <h1 [innerHTML]="item?.title.rendered"></h1>
      <div [innerHTML]="item?.content.rendered"></div>
    </div>
  </div>
</app-private-nav>
</code>```

Body:
Here's my situation: I'm working with Wordpress API in a XAMMP local server as backend with Angular 8 frontend.
When I request all the posts, it works, but when I request about only one post, to show it into the component, I got this error:

ERROR TypeError: "_co.item is undefined"

View_PostsDetailComponent_0 PostsDetailComponent.html:5

Here is PostDetailComponent.html

<app-private-nav>

</app-private-nav>

I´m not using a model, but it suposed to work anyway.

import { Component, OnInit, Input } from '@angular/core';
import { PostsService } from './../posts.service';
import { ActivatedRoute, Router } from '@angular/router';

@Component({
  selector: 'app-posts-detail',
  templateUrl: './posts-detail.component.html',
  styleUrls: ['./posts-detail.component.scss']
})

export class PostsDetailComponent implements OnInit {
  item: any;
  id: number;

  constructor(private route: ActivatedRoute, public dataService: PostsService) { }
  ngOnInit() {

    this.route.url.subscribe(u => {
      this.id = this.route.snapshot.params.id;
    });

    this.dataService.getPostByID(this.id).subscribe((data: any) => {
      this.item = data;
    });
  }

I'm using this url for the Wordpress API

wordpress/wp-json/wp/v2/posts/[ID]?_embed

And here is the PostService:

export class PostsService {
     item: any;
  constructor(private http: HttpClient) { }

  getPostByID(id: number): any {
    if (this.item) {
      return of(this.item);
    } else {
      return this.http.get(ENDPOINT_URL + 'wp/v2/posts' + `/${id}` + '?_embed').pipe(map(this.processPostDataID, this));
    }
    }

  processPostDataID(data: any) {
    this.item = data;

     return this.item;
      }
}

Thanks a lot.

Body:
The item isn't set to anything when the HTML first loads - it takes a small amount of time for the observable to return the item.

You can wrap the item calls with *ngIf. This will prevent "item.title.rendered" trying to access an undefined object.

<app-private-nav>
  <div class="page-wrapper" id="main">
    <div *ngIf="item" fxLayout="row wrap" fxLayoutGap="16px" >
      <h1 [innerHTML]="item.title.rendered"></h1>
      <div [innerHTML]="item.content.rendered"></div>
    </div>
  </div>
</app-private-nav>

You can also use optional chaining to handle individual calls. Optional chaining makes expressions operating on undefined or null objects safe.

<app-private-nav>
  <div class="page-wrapper" id="main">
    <div fxLayout="row wrap" fxLayoutGap="16px" >
      <h1 [innerHTML]="item?.title.rendered"></h1>
      <div [innerHTML]="item?.content.rendered"></div>
    </div>
  </div>
</app-private-nav>


问:
upper casing all values within quotes " " except those coming after certain words
I am trying to replace every word within quotes " " to upper case word except those coming after the word "then" in a pandas column:
for example:

```<code>0 There was a "quick" "brown" fox who "jumped" over the wall then "fell" and broke its "tooth"
</code>```

the output should be:

```<code>0 There was a "QUICK" "BROWN" fox who "JUMPED" over the wall then "fell" and broke its "TOOTH"
</code>```

although I am able to find the words in quotes but I am not able to exclude the word coming right after "then".

```<code>df.str.replace({r'"(.*?)"':r'\U$1') #this will select and replace all values in quotes to uppercase also values after then
</code>```

please help.

答:
You can use regex (?<!then\s)"(\w*)" to find the words within quotes that are NOT preceded by 'then' & 'space'

"(\w*)" = Look for words within quotes

(?<!then\s) = Make sure the words that are matched with "(\w*)"does not have 'then' & 'space' before it(Negative look-behind)

RegexDemo You can see the demo of the regex here (you can put several other string to check how the regex works on them as well)

Regex-info This is very comprehensive website (kind of the go-to website for all things regex) on regex, almost all concepts of regex should be answered here. It is not programming language dependent & has a lot of information which can be overwhelming.

Regex Cheat-Sheet I would say start with this cheat sheet, it is very simple & explained in simple words. I find it very helpful.

Body:
I am trying to replace every word within quotes " " to upper case word except those coming after the word "then" in a pandas column:
for example:

0 There was a "quick" "brown" fox who "jumped" over the wall then "fell" and broke its "tooth"

the output should be:

0 There was a "QUICK" "BROWN" fox who "JUMPED" over the wall then "fell" and broke its "TOOTH"

although I am able to find the words in quotes but I am not able to exclude the word coming right after "then".

df.str.replace({r'"(.*?)"':r'\U$1') #this will select and replace all values in quotes to uppercase also values after then

please help.


答: Doxygen allows you to generate interactive class diagrams. Check out this page: http://www.doxygen.nl/manual/diagrams.html how to set it up.</p>

Title: Software for browsing classes in a C/C++ project

Body:
I have seen that some developers have a graphical representation of all their classes in an image file which comes with their project. How can I myself create these graphics?

Basically they show what classes exist in a file and how these files relate to each other.

Thanks


问:
Cannot select a cell in Pandas
It is probably a very stupid thing but I am really going mad about this.
Let's say that I have the following dataframe (the table is actually much bigger but this is not important):

```<code>Index   Call Rate   Array Info.X    Array Info.Y
    0   "0.993922"  20202821    R01C01
    1   "0.723922"  20217347    R01C01
    2   "0.493922"  20257906    R01C01
    3   "0.723922"  20355030    R01C01
</code>```

I am trying to use the following code to extract the value "Call Rate" for the combination "20257906" (folder) & R01C01 (current_section).

```<code>call_rate = samples_table.loc[((samples_table['Array Info.Y'] == current_section))]# & (samples_table['Array Info.X'] == (f"{folder}")),'Call Rate']
call_rate = float(numpy.round(call_rate, decimals=4))
</code>```

However, the variable call_rate appears to be empty so there's nothing to round.
The information is decently in the dataframe so I assume that this is caused by something wrong in the first line of my code.

I am using Spyder and the variable explorer describes the variable "call_rate" as:

```<code>Series; (0,); Series of pandas.core.series module
</code>```

Could someone please help me?

答:
You almost had it! The following should give you what you desire:</p>

```<code>current_section = 'R01C01'
folder = 20257906

call_rate = samples_table[(samples_table['Array Info.X'] == folder) & (df['Array Info.Y'] == current_section)]['Call Rate']

call_rate = np.round(float(call_rate.iloc[0]), decimals=4)
</code>```

Title: Cannot select a cell in Pandas

Body:
It is probably a very stupid thing but I am really going mad about this.
Let's say that I have the following dataframe (the table is actually much bigger but this is not important):

Index   Call Rate   Array Info.X    Array Info.Y
    0   "0.993922"  20202821    R01C01
    1   "0.723922"  20217347    R01C01
    2   "0.493922"  20257906    R01C01
    3   "0.723922"  20355030    R01C01

I am trying to use the following code to extract the value "Call Rate" for the combination "20257906" (folder) & R01C01 (current_section).

call_rate = samples_table.loc[((samples_table['Array Info.Y'] == current_section))]# & (samples_table['Array Info.X'] == (f"{folder}")),'Call Rate']
call_rate = float(numpy.round(call_rate, decimals=4))

However, the variable call_rate appears to be empty so there's nothing to round.
The information is decently in the dataframe so I assume that this is caused by something wrong in the first line of my code.

I am using Spyder and the variable explorer describes the variable "call_rate" as:

Series; (0,); Series of pandas.core.series module

Could someone please help me?

Body:
You almost had it! The following should give you what you desire:

current_section = 'R01C01'
folder = 20257906

call_rate = samples_table[(samples_table['Array Info.X'] == folder) & (df['Array Info.Y'] == current_section)]['Call Rate']

call_rate = np.round(float(call_rate.iloc[0]), decimals=4)


问:
laravel paginate a query that has a limit clause
You have a database of 10,000 customers.  You want to limit your search to only 500 of those customers but want to paginate the results by 50 results each page.  So the last page, page 10, of the results would have 50 results.  There would be no page 11.

Is this possible with eloquent/laravel?  Something like "select * from people order by field desc limit 500" and then perform pagination on those results?

Paginate seems to use ->take() and ->offset(), which ignore ->limit().  I'd like to be able to use all three.

答:
With a subquery

```<code>        $events = DB::table(function ($query) {
            $query->selectRaw('*')
                ->from('system_events_collected')
                ->orderBy('event_at','desc')
                ->take(500);
        })->paginate(10);
</code>```

in this case, the data I want to paginate is in a table called system_events_collected.  A subquery gets the first 500 rows from this table, then pagination is performed on the results.

Body:
With a subquery

        $events = DB::table(function ($query) {
            $query->selectRaw('*')
                ->from('system_events_collected')
                ->orderBy('event_at','desc')
                ->take(500);
        })->paginate(10);

in this case, the data I want to paginate is in a table called system_events_collected.  A subquery gets the first 500 rows from this table, then pagination is performed on the results.


问:
How to show a link if user_id =1 in HTML with PHP
I am new and learning PHP and HTML. I have one user table where I am displaying data. I want to show Delete button if user_id = 1. else I want to hide it. My current link code is like below

```<code><a href="<?php echo site_url('members/delete/'.$row['user_id']); ?>" class="btn btn-danger" onclick="return confirm('Are you sure to delete?')">delete</a>
</code>```

I have done PHP code for doing achieve my above description

```<code><?php
if ($row['user_id']==1){
echo //// I want a link here
}
?>
</code>```

But since my link have also used some PHP codes, I am confused and not able to properly echo button. I am trying from an hour and not able to fix it. Let me know if someone can help me for do it.

Thanks!

答:
you can put html code in echo like this </p>

```<code> echo "<tag name></tag name>"
</code>```

Body:
I am new and learning PHP and HTML. I have one user table where I am displaying data. I want to show Delete button if user_id = 1. else I want to hide it. My current link code is like below

<a href="<?php echo site_url('members/delete/'.$row['user_id']); ?>" class="btn btn-danger" onclick="return confirm('Are you sure to delete?')">delete

I have done PHP code for doing achieve my above description

<?php
if ($row['user_id']==1){
echo //// I want a link here
}
?>

But since my link have also used some PHP codes, I am confused and not able to properly echo button. I am trying from an hour and not able to fix it. Let me know if someone can help me for do it.

Thanks!

Body:
you can put html code in echo like this

 echo "<tag name></tag name>"


问:
Office 365 Groups - Create Default OneNote Document
We're creating Office 365 Groups using a PowerShell script, and this process in place already and we're happy with it.

I was wondering however, if it was possible to trigger the creation of the default OneNote document via the Graph API at all? It appears as though when a Group is created, it's not until someone manually visits the Document library and clicks the OneNote document, is it actually created (on the fly). I have arrived at this conclusion because if we run a Get-UnifiedGroup on the group itself (storing the result in $Group), the following property is empty: $Group.SharePointNotebookUrl

The reason we'd like to do this, is so we can create Office 365 Groups and populate the default OneNote notebook with some information, without having to have a user with permission to the group, manually trigger the creation of it. I'm able to create additional notebooks in the Group, but these are not 'presented' in the same way to end users that the default one is (with it's own dedicated tabs/menu links).

答:
Office 365 default Notebook is a file in SiteAssets library in the associated SharePoint site.

If Notebook is not created yet, you can create by enabling SharePoint Feature SiteNotebook with Id f151bb39-7c3b-414f-bb36-6bf18872052f

With PnP PowerShell:

Enable-PnPFeature -Identity f151bb39-7c3b-414f-bb36-6bf18872052f -Scope Web
```

This will create Notebook in SiteAssets library and adds PropertyBag to SPWeb with name "SiteNotebookGuid".

Body:
Office 365 default Notebook is a file in SiteAssets library in the associated SharePoint site.

If Notebook is not created yet, you can create by enabling SharePoint Feature SiteNotebook with Id f151bb39-7c3b-414f-bb36-6bf18872052f

With PnP PowerShell:

Enable-PnPFeature -Identity f151bb39-7c3b-414f-bb36-6bf18872052f -Scope Web

This will create Notebook in SiteAssets library and adds PropertyBag to SPWeb with name "SiteNotebookGuid".


问:
Joomla, Multiple layouts in a single template
I tired the joomla IRC but no one ever talks in there :(, heres the issue i have.

i have a site that have 3 diff types of layouts.

home page

all the other pages

portfolio page

im using joomla 1.6

all i can see is the index.php file on the root of my template folder.
hwo can i create 3 diff layouts in side a single template folder and assign these to diff pages (menu itmes).

答:
From : http://docs.joomla.org/Introduction_to_Joomla!_templates#Assigning_more_than_one_template_for_the_Front-end

Assigning more than one template for the Front-end

Joomla! gives you the option of switching between two or more templates on the Front-end. This is done with "Menu Assignment", so you can set different templates for each menu or only a menu item.

Click on: Extensions -> Template Manager

You see your installed templates, now choose a template which is not set as your default one. Click on the template name or hit the radio button to the left of the template name, and click "Edit" on the toolbar. You now see the page "Template: [ Edit ]", click the radio button "Select from List" in the "Menu Assignment" block and choose the menu items on which the template will be shown. Click "Save" on the toolbar and you´re done.


问:
Ist it possible to combine dimensions and metrics in calculated fields?
We have the variables:

"Unique User"

"Version" (Plus, Light in a ratio 79:21 from all Unique User)

"total Events"

"Eventkatagories".

And following scenario:
We can't get the exact data how many users are plus or light users.
But we know how many events are triggered by version (plus/light).

Now we want to know how the relative frequency of events triggered grouped by Version and event category.
So in a pivot table there is the row dimension = Version and the column Dimension = event category.
So the measurement should be the relative frequency.

So the simple custom calculated field should be "total events / users"... But remember we can't get the absolute value of Users by Version, we just know the ratio (80-20).

So I build another calculated field called UsersbyVersion with following statement:

```<code>CASE
  WHEN (Version = "light") THEN SUM(User) * 0.21
  WHEN (Version = "Plus") THEN SUM(User) * 0.79
END
</code>```

But this formula gives following error:

Invalid formula - Invalid input expression. - Failed to parse CASE
  statement

If I use absolute numbers for the statement it works.
Example:

```<code>CASE
  WHEN (Version = "Normal") THEN 5000
  WHEN (Version = "Plus") THEN 25000
END
</code>```

But we need the statement "User * ration" ... the ratio won't change a lot but the user value in relation to the date we want to set on the Data Studio Report.

So I guess the problem is that the statement won't work with a combination of metrics and dimensions.
I already tried putting the "User * 0.79" and "User * 0.21" in custom metrics but this won't work aswell.

Is there a way to combine dimensions and metrics in a calculated field as an measurement?

Thx for your help

答:
Create 2 metrics -

users * 0.2 (lets call this UsersP2)

users * 0.8 (lets call this UsersP8)

Now this should work

```<code>CASE
  WHEN (Version = "light") THEN UserP2
  WHEN (Version = "Plus") THEN UserP8
END
</code>```

Dataset

Result

Body:
We have the variables:

"Unique User"

"Version" (Plus, Light in a ratio 79:21 from all Unique User)

"total Events"

"Eventkatagories".

And following scenario:
We can't get the exact data how many users are plus or light users.
But we know how many events are triggered by version (plus/light).

Now we want to know how the relative frequency of events triggered grouped by Version and event category.
So in a pivot table there is the row dimension = Version and the column Dimension = event category.
So the measurement should be the relative frequency.

So the simple custom calculated field should be "total events / users"... But remember we can't get the absolute value of Users by Version, we just know the ratio (80-20).

So I build another calculated field called UsersbyVersion with following statement:

CASE
  WHEN (Version = "light") THEN SUM(User) * 0.21
  WHEN (Version = "Plus") THEN SUM(User) * 0.79
END

But this formula gives following error:

Invalid formula - Invalid input expression. - Failed to parse CASE
  statement

If I use absolute numbers for the statement it works.
Example:

CASE
  WHEN (Version = "Normal") THEN 5000
  WHEN (Version = "Plus") THEN 25000
END

But we need the statement "User * ration" ... the ratio won't change a lot but the user value in relation to the date we want to set on the Data Studio Report.

So I guess the problem is that the statement won't work with a combination of metrics and dimensions.
I already tried putting the "User * 0.79" and "User * 0.21" in custom metrics but this won't work aswell.

Is there a way to combine dimensions and metrics in a calculated field as an measurement?

Thx for your help

Body:
Create 2 metrics -

users * 0.2 (lets call this UsersP2)

users * 0.8 (lets call this UsersP8)

Now this should work

CASE
  WHEN (Version = "light") THEN UserP2
  WHEN (Version = "Plus") THEN UserP8
END

Dataset

Result


答:
If I understood you correctly, you can do in this way:

```<code>current_section = 'R01C01'
folder = 20257906
call_rate = samples_table.loc[(samples_table['Array Info.Y'] == current_section) & (samples_table['Array Info.X'] == folder),'Call Rate']
call_rate = numpy.round(float(call_rate.iloc[0][1:-1]), decimals=4)
print (call_rate)
</code>```

Output:

```<code>0.4939
</code>```

Title: Cannot select a cell in Pandas

Body:
If I understood you correctly, you can do in this way:

current_section = 'R01C01'
folder = 20257906
call_rate = samples_table.loc[(samples_table['Array Info.Y'] == current_section) & (samples_table['Array Info.X'] == folder),'Call Rate']
call_rate = numpy.round(float(call_rate.iloc[0][1:-1]), decimals=4)
print (call_rate)

Output:

0.4939


问:
Exporting data from outlook to excel with parsing
I receive a form in email that looks as follows.

The submitted details are below:

```<code>Region Europe
Country Spain
Contactable by email no
Contactable by phone no
Title MR
First name John
Last name Doe
Email j.doe@doe.com
Contact number 1234567
Role Customer
Institution companyname ltd
Product TEST product
Message
TEST Question
</code>```

I need to extract some of the fields into the Excel Workbook.

I have been entering those emails manually so I need to find a code to extract data into next empty row.

My Excel headers look like this

Excel columns

I have checked some of the answers here and I managed to put together a code but it doesn't seem to work as needed.

Here what I have so far.

I entered it into Outlook VBA module.

```<code>Sub Extract()

    Dim myOlApp As Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim topOlFolder As Outlook.MAPIFolder
    Dim myOlFolder As Outlook.Folder
    Dim myOlMailItem As Outlook.MailItem

    Set myNameSpace = Outlook.Application.GetNamespace("mapi")
    Set objItem = objApp.ActiveExplorer.Selection.Item(1)

    Dim xlObj As worksheet
    Set xlObj = ActiveSheet

    Dim anchor As Range
    Set anchor = xlObj.Range("b2")

        anchor.Offset(0, 0).Value = "Country"
    anchor.Offset(0, 1).Value = "Role"
    anchor.Offset(0, 2).Value = "Product"
    anchor.Offset(0, 3).Value = "Message"
    anchor.Offset(0, 4).Value = "Sender"

    Dim msgText As String
    Dim msgLine() As String
    Dim messageArray() As String

    i = 0
    For Each myOlMailItem In myOlFolder.Items
        i = i + 1

        msgText = myOlMailItem.Body

        messageArray = Split(msgText, vbCrLf)

        For j = 0 To UBound(messageArray)

            msgLine = Split(messageArray(j) & ":", ":")

            Select Case Left(msgLine(0), 6)

                Case "Countr"
                    anchor.Offset(i, 0).Value = msgLine(1)

                Case "Role"
                    anchor.Offset(i, 1).Value = messageArray(j + 1)

                Case "Product"
                    anchor.Offset(i, 2).Value = messageArray(j + 1)

                Case "Message"
                    anchor.Offset(i, 3).Value = msgLine(1)

            End Select
            anchor.Offset(i, 4).Value = myOlMailItem.SenderName
            anchor.Offset(i, -1).Value = i

        Next
    Next
End Sub
</code>```

I would really appreciate your help and comments.

Edit:

I have run the diagnostics tool and this is how email body looks like:

```<code>     |Message ‹2 crlf›|
      |TEST question - please confirm receipt if received. ‹2 crlf›|
      |AAA-BBB-001366 ‹2 crlf›|
      |JAN 2020 ‹2 crlf›|
      | ‹2 crlf›|
      |info.com <http://info.com/?mc_phishing_protection_id=xxxbpg|
      |db3lse2ip1c6bc0n0>‹2 s›‹2 crlf›|
      | ‹2 crlf›|
      | ‹3 crlf›|
      |This email (including any attachment) is intended only for the use by the recipients named above and|
      | contains proprietary information that may be confidential, copyrighted and/or privileged. Unauthori|
      |zed disclosure, use or copying is prohibited. If this email was sent to you in error or if you are n|
      |ot an intended recipient, please notify the sender immediately and delete this e-mail from your syst|
      |ems. Thank you‹crlf›|
Html: |<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmln|
      |s:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omm|
      |l" xmlns="http://www.w3.org/TR/REC-html40"><head>‹2 crlf›|
      |<meta name="Generator" content="Microsoft Word 15 (filtered medium)">‹crlf›|
      |<title>Simple Transactional Email</title>‹crlf›|
      |<style><!--‹crlf›|
      |/* Font Definitions */‹crlf›|
      |@font-face‹crlf›|
      |‹tb›{font-family:"Cambria Math";‹crlf›|
      |‹tb›panose-1:2 4 5 3 5 4 6 3 2 4;}‹crlf›|
      |@font-face‹crlf›|
      |‹tb›{font-family:Calibri;‹crlf›|
      |‹tb›panose-1:2 15 5 2 2 2 4 3 2 4;}‹crlf›|
      |/* Style Definitions */‹crlf›|
      |p.MsoNormal, li.MsoNormal, div.MsoNormal‹crlf›|
      |‹tb›{margin:0cm;‹crlf›|
      |‹tb›margin-bottom:.0001pt;‹crlf›|
      |‹tb›font-size:11.0pt;‹crlf›|
      |‹tb›font-family:"Calibri",sans-serif;}‹crlf›|
      |a:link, span.MsoHyperlink‹crlf›|
      |‹tb›{mso-style-priority:99;‹crlf›|
      |‹tb›color:blue;‹crlf›|
      |‹tb›text-decoration:underline;}‹crlf›|
      |a:visited, span.MsoHyperlinkFollowed‹crlf›|
      |‹tb›{mso-style-priority:99;‹crlf›|
      |‹tb›color:purple;‹crlf›|
      |‹tb›text-decoration:underline;}‹crlf›|
      |p.msonormal0, li.msonormal0, div.msonormal0‹crlf›|
      |‹tb›{mso-style-name:msonormal;‹crlf›|
      |‹tb›mso-margin-top-alt:auto;‹crlf›|
      |‹tb›margin-right:0cm;‹crlf›|
      |‹tb›mso-margin-bottom-alt:auto;‹crlf›|
      |‹tb›margin-left:0cm;‹crlf›|
      |‹tb›font-size:11.0pt;‹crlf›|
      |‹tb›font-family:"Calibri",sans-serif;}‹crlf›|
      |span.preheader‹crlf›|
      |‹tb›{mso-style-name:preheader;}‹crlf›|
      |span.EmailStyle19‹crlf›|
      |‹tb›{mso-style-type:personal;‹crlf›|
      |‹tb›font-family:"Calibri",sans-serif;‹crlf›|
      |‹tb›color:windowtext;}‹crlf›|
      |span.EmailStyle22‹crlf›|
      |‹tb›{mso-style-type:personal-reply;‹crlf›|
      |‹tb›font-family:"Calibri",sans-serif;‹crlf›|
      |‹tb›color:windowtext;}‹crlf›|
      |.MsoChpDefault‹crlf›|
      |‹tb›{mso-style-type:export-only;‹crlf›|
      |‹tb›font-size:10.0pt;}‹crlf›|
      |@page WordSection1‹crlf›|
      |‹tb›{size:612.0pt 792.0pt;‹crlf›|
      |‹tb›margin:72.0pt 72.0pt 72.0pt 72.0pt;}‹crlf›|
      |div.WordSection1‹crlf›|
      |‹tb›{page:WordSection1;}‹crlf›|
      |--></style><!--[if gte mso 9]><xml>‹crlf›|
      |<o:shapedefaults v:ext="edit" spidmax="1026" />‹crlf›|
      |</xml><![endif]--><!--[if gte mso 9]><xml>‹crlf›|
      |<o:shapelayout v:ext="edit">‹crlf›|
      |<o:idmap v:ext="edit" data="1" />‹crlf›|
      |</o:shapelayout></xml><![endif]-->‹crlf›|
      |</head>‹crlf›|
      |<body bgcolor="#F6F6F6" lang="EN-US" link="blue" vlink="purple">```
|
      |CAUTION:This email originated from outside of |
      |the Vifor Pharma Group organisation. Do not click on links or open attachments unless you recognise |
      |the sender and know the content is safe.
‹crlf›|
      |```‹crlf›|
      |<div class="WordSection1">‹crlf›|
      |<p class="MsoNormal"><o:p> </o:p></p>‹crlf›|
      |<p class="MsoNormal"><o:p> </o:p></p>‹crlf›|
      |<p class="MsoNormal"><o:p> </o:p></p>‹crlf›|
      |<div>‹crlf›|
      |<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">‹crlf›|
      |<p class="MsoNormal"><b>From:</b> Info GM  <noreply@info.com>‹crlf›|
      |<br>‹crlf›|
      |<b>Sent:</b> 19 February 2020 16:20<br>‹crlf›|
      |<b>To:</b> Info1 <pharma|
      |info2@info.com><br>‹crlf›|
      |<b>Cc:</b> GM <GlobalMedInfo@info|
      |.com><br>‹crlf›|
      |<b>Subject:</b> New query received<o:p></o:p></p>‹crlf›|
      |</div>‹crlf›|
      |</div>‹crlf›|
      |<p class="MsoNormal"><o:p> </o:p></p>‹crlf›|
      |<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="100%" style="width:1|
      |00.0%;background:#F6F6F6">‹crlf›|
      |<tbody>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 0cm 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif"> <|
      |o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td width="580" valign="top" style="width:435.0pt;padding:7.5pt 7.5pt 7.5pt 7.5pt">‹crlf›|
      |<div>‹crlf›|
      |<p class="MsoNormal"><span class="preheader"><span style="font-size:10.5pt;font-family:"Arial&q|
      |uot;,sans-serif">New query received</span></span><span style="font-size:10.5pt;font-family:"Ari|
      |al",sans-serif">‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |<table class="MsoNormalTable" border="0" cellspacing="3" cellpadding="0" width="100%" style="width:1|
      |00.0%;background:white;border-radius: 3px">‹crlf›|
      |<tbody>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:15.0pt 15.0pt 15.0pt 15.0pt;box-sizing: border-box">‹crlf›|
      |<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="100%" style="width:1|
      |00.0%;box-sizing: border-box">‹crlf›|
      |<tbody>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 0cm 0cm">‹crlf›|
      |<p style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:11.25pt;margin-left:0cm">‹crlf›|
      |<span style="font-size:10.5pt;font-family:"Arial",sans-serif">New product complaint query |
      |received from info.com<o:p></o:p></span></p>‹crlf›|
      |<p style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:11.25pt;margin-left:0cm">‹crlf›|
      |<span style="font-size:10.5pt;font-family:"Arial",sans-serif">The submitted details are be|
      |low:<o:p></o:p></span></p>‹crlf›|
      |<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="100%" style="width:1|
      |00.0%;background:white;box-sizing: border-box">‹crlf›|
      |<tbody>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Region|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Europe|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Country|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Switzer|
      |land‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Title|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Ms|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">First n|
      |ame‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Joe|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Last na|
      |me‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Doe|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Email|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Joe.Doe.ext@info.com‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Contact|
      | number‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm"></td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Role|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Other|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Institu|
      |tion‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm"></td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Product|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |<td valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">TEST pr|
      |oduct‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td colspan="2" valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial",sans-serif">Message|
      |‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td colspan="2" valign="top" style="padding:0cm 0cm 11.25pt 0cm">‹crlf›|
      |<p class="MsoNormal"><i><span style="font-size:10.5pt;font-family:"Arial",sans-serif">TEST|
      | question - please confirm receipt if received.‹crlf›|
      |<o:p></o:p></span></i></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td colspan="2" valign="top" style="padding:0cm 0cm 0cm 0cm">‹crlf›|
      |<p class="MsoNormal" align="right" style="text-align:right"><span style="font-size:10.5pt;font-famil|
      |y:"Arial",sans-serif">ALL-ALL-001366‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |<tr>‹crlf›|
      |<td colspan="2" valign="top" style="padding:0cm 0cm 0cm 0cm">‹crlf›|
      |<p class="MsoNormal" align="right" style="text-align:right"><span style="font-size:10.5pt;font-famil|
      |y:"Arial",sans-serif">JAN 2020‹crlf›|
      |<o:p></o:p></span></p>‹crlf›|
      |</td>‹crlf›|
      |</tr>‹crlf›|
      |</tbody>‹crlf›|

</code>```

I have run the tool and here it the text:

@Tony Dallimore Here is the text `Text: |From: Info <noreply@info.com <mailto:noreply@info.com> > ‹crlf›|
      |Sent: 19 February 2020 16:20‹crlf›|
      |To: Email1 <email1@info.com <mailto:email1|
      |@info.com> >‹crlf›|
      |Cc: Infor <Info1@infor.com <mailto:infor1@info.com> >‹crlf›|
      |Subject: New query received‹2 crlf›|
      | ‹2 crlf›|
      | ‹2 crlf›|
      |New query received ‹2 crlf›|
      |New product complaint query received from info.com‹2 crlf›|
      |The submitted details are below:‹2 crlf›|
      |Region ‹2 crlf›|
      |Europe ‹2 crlf›|
      |Country ‹2 crlf›|
      |Switzerland ‹2 crlf›|
      |Title ‹2 crlf›|
      |Mr ‹2 crlf›|
      |First name ‹2 crlf›|
      |Joe ‹2 crlf›|
      |Last name ‹2 crlf›|
      |Doe ‹2 crlf›|
      |Email ‹2 crlf›|
      |joedoe@info.com <mailto:joedoe@info.com>‹2 s›‹2 crlf›|
      |Contact number ‹2 crlf›|
      |‹tb›‹crlf›|
      |Role ‹2 crlf›|
      |Other ‹2 crlf›|
      |Institution ‹2 crlf›|
      |‹tb›‹crlf›|
      |Product ‹2 crlf›|
      |TEST product ‹2 crlf›|
      |Message ‹2 crlf›|
      |TEST question - please confirm receipt if received. ‹2 crlf›|
      |AAA-BBB-001366 ‹2 crlf›|
      |JAN 2020 ‹2 crlf›|
      | ‹2 crlf›|

答:
I have added Option Explicit to the top of your code.  This is very helpful in finding compile-time errors.

```<code>Dim myOlApp As Outlook.Application
Set objItem = objApp.ActiveExplorer.Selection.Item(1)
</code>```

You declare myOlApp but you use objApp without initialising it.

```<code>Dim myOlMailItem As Outlook.MailItem
Set objItem = myOlApp.ActiveExplorer.Selection.Item(1)
</code>```

You declare myOlMailItem but you use objItem.

```<code>i = 0
For j = 0 To UBound(messageArray)
</code>```

You do not declare i or j.  If you omit Option Explicit, you do not have to declare every variable because they will be declared for you as type Variant.  However, this means every minor spelling mistake becomes a new variable.  For example:

```<code>Count = Conut +1
</code>```

Conut will be declared as a new variable and initialised to zero.

I do not get “User-defined type not defined”.  Thinking about this made me realise I had made a false assumption about where this code is placed.

You write, for example, Dim myNameSpace As Outlook.NameSpace and Dim anchor As Range.

You can write VBA within Outlook to access an Excel worksheet or you can write VBA within Excel to access an Outlook folder.  If you are within Outlook, you must tell the Outlook VBA compiler that you want to access Excel.  And vice versa.  You prefix NameSpace but you do not prefix Range.  I assumed this code was within an Excel workbook.  But on reflection, I now believe this code is within Outlook.  On rereading your question, I see that you say it is Outlook VBA; I missed that on my first reading.

It does not really matter where this code is placed; it cannot work.  If you are within Outlook, you cannot reference the active worksheet without opening an Excel workbook first.  If you are within Excel, which email has the user selected?

Let us step back from the code and consider your design options.

Using ActiveExplorer is my favourite method of testing a new email processing macro.  I can select a simple email for my first test.  I can correct the macro and rerun it against the same email as often as I wish.  Once the macro works on simple emails, I can try it on more complicated ones.  However, once my new macro is working properly, I want to automate the selection.  I do not want to think: “It is 12:00, have any of these emails arrived this morning?  If so, I need to go into to Outlook, find any that have arrived and process them.”  I want to think: “It is 12:00, have any new emails been copied to my Excel workbook.”

There are four distinct methods of selecting an email for processing.  You cannot mix and match.  These methods are:

User selection which is the method you are trying.

You can read down a folder (Oldest to Newest) or up a folder (Newest to Oldest) or you can sort the folder by any of the regular properties.

You can create a Rule which tells Outlook to monitor Inbox for particular emails and do something when one arrives.

You can use events to tell Outlook to call a macro whenever a new email is placed in a particular folder.

With method 1, it is the user that identifies an email to be processed.  With the other methods, there must be a test a macro can perform.  Are these emails all from the same sender?  Do these emails have a particular subject or a particular phrase in the subject?  Failing a simple check, you can look in the body for “Region”, “Country” and “Contactable by”.

A problem with methods 3 and 4, is that the macro is called when one of these emails arrive.  The macro must quickly open the workbook, update it and close it.  What is to happen if an email arrives while the workbook is being processed by a user?  I would not want to tackle this issue if I was new to VBA as you are.

I would use method 2.  I would have an Excel workbook that contained a macro that accessed Outlook’s Inbox and looked through the newest emails for any of the target emails.  This is not quite what I said I wanted but it is close enough.  Every time I open the workbook, I can, if I wish, run the macro to process any new emails.  This should only take a few seconds if the macro is run regularly.

It might be worth using a rule to move these emails to a dedicated folder.  This would mean that the macro does not have to search through Inbox for these emails.  This may look as though I am mixing methods 2 and 3 but not in the same macro.  Whenever, you open Outlook new emails arrive from the server and the rule will move any target emails to their folder.  When you open the workbook, you can decide to check for new target emails or review those emails already recorded in the workbook.

I think that is enough for you to think about for the moment.  Review the approaches I have discussed and come to some decisions about what you want.

Part 2

I have not had the free time I expected this evening so I doubt I will finish today.  I also have the problem that I should have asked more questions about your requirement.

To give you something to look at tomorrow, I have produced a demo macro which includes a lot of what you need to know and which I think you will find helpful in understanding the final macro.

I have created a workbook and worksheet with a heading that matches that shown in your question.  I have named the workbook “Email Data.xlsx” and have placed it on my desktop.  I have named the worksheet “Email Data”.  I assume you have different names and a different location.  The macro tells you what to change.

An Outlook macro that wants to access an Excel workbook, must have access to the Excel library.  You might know how to add references but just in case:

Open Outlook VBA Editor.

Click [Tools] in the Standard Toolbar then [References…].

A list of Available References is displayed.  Some available references at the top (such as Visual Basic for Application) are ticked.  Most references are not ticked.

If “Microsoft Excel nn.n Object Library” is ticked, click [Cancel] because there is nothing to do.  Note: the value of “nn.n” depends on the version of Outlook and Excel you are using.  This code should work with any version of Excel, so it does not matter if you are using an old version.

If “Microsoft Excel nn.n Object Library” is not ticked, scroll down the list until you find this reference and click the box n the left to tick it.

Click [OK].

Repeat step 2 and check that “Microsoft Excel nn.n Object Library” now appears at the top of the list and is ticked.

Copy this macro to an Outlook module:

```<code>Option Explicit
Sub DemoOpenWorkbook()

  ' Needs reference to Microsoft Excel n.nn Object Library
  ' where n.nn depends on the version of Office being used

  Dim Path As String
  Dim WbkEmailData As Excel.Workbook
  Dim WshtEmailData As Excel.Worksheet
  Dim XlApp As New Excel.Application

  ' Replace with path to the folder which holds your workbook
  Path = CreateObject("WScript.Shell").specialfolders("Desktop")

  With XlApp
    .Visible = True   ' Slows your application but makes debugging easier
    ' Replace "Email Data.xlsx" with the name of your workbook
    Set WbkEmailData = .Workbooks.Open(Path & "\Email Data.xlsx")
  End With

  With WbkEmailData
    ' Replace "Email Data" with the name of your worksheet
    Set WshtEmailData = .Worksheets("Email Data")
  End With

  With WshtEmailData
    Debug.Print .Cells(1, 1).Value
    Debug.Print .Cells(1, 2).Value
    Debug.Print .Cells(1, 3).Value
  End With

  WbkEmailData.Close
  Set WshtEmailData = Nothing
  Set WbkEmailData = Nothing
  XlApp.Quit
  Set XlApp = Nothing

End Sub
</code>```

Make the changes I have discussed above.  Run the macro.  The workbook is opened, the first three headings from the worksheet are output to the Immediate Window and the Workbook is closed.  The Immediate Window will now contain:

```<code>DATE OF RECEIPT
DELIVERY DATE
MONTH
</code>```

Work down my code.  If you do not understand any of my statements, look them up.  In general, typing something like “Outlook VBA Name-of-statement-you-do-not-understand” into your favourite search engine will find an explanation.  If necessary, come back with questions but I hope you can understand why this code works without any more help from me.

Note: you do not need to remember this code.  There are VBA statements and blocks of VBA code that I type often enough to remember them.  But I am not ashamed to look up something I use infrequently or to look back at a working macro that does something similar to what I want to do today.

Part3

The next two macros demonstrate my favourite technique for testing a new email processing macro.  Near the bottom of TestNewEmailProcessingMacro, you will find the statement Call EmailProcessingMacro(ItemCrnt).  When I create a new email processing macro, I amend this statement to call my new macro.  I then select a simple email of the type my macro processes before running TestNewEmailProcessingMacro().  I carefully check my macro processes the simple email correctly.  If not, I can correct the macro and rerun the test as often as necessary.  Once my macro processes a simple email correctly, I can try more complicated emails.  I continue until my macro performs to my entire satisfaction.  I would then call my new macro from a loop, a rule or an event as explained in the first part of my answer.

```<code>Sub TestNewEmailProcessingMacro()

  Dim Exp As Explorer
  Dim ItemCrnt As MailItem

  Set Exp = Outlook.Application.ActiveExplorer

  If Exp.Selection.Count = 0 Then
    Call MsgBox("Please select one or more emails then try again", vbOKOnly)
    Exit Sub
  Else
    For Each ItemCrnt In Exp.Selection
      If ItemCrnt.Class = olMail Then
        Call EmailProcessingMacro(ItemCrnt)
      End If
    Next
  End If

End Sub
Public Sub EmailProcessingMacro(ByRef ItemCrnt As MailItem)

  With ItemCrnt
    Debug.Print .ReceivedTime & " " & .Subject
  End With

End Sub
</code>```

The above code shows how to use Explorer correctly.  In your code you have Set objItem = objApp.ActiveExplorer.Selection.Item(1).  This attempts to access the first selected email even if none are selected and ignores any further emails that have been selected.

I will next show you how to combine the techniques shown in the above macros.

Part 4

Below are the two macros which together do what I think you want.

You will need to make the same changes that were necessary with DemoOpenWorkbook().  That is, you will have to change the path, the name of the workbook and the name of the worksheet.  You will not need to add another reference to the Excel library, once is enough.

Select one or more of these emails and then run macro CtrlCopyEmailDataToExcel().  This is the macro which opens the workbook and then saves the changes.  This macro calls CopyEmailDataToExcel() for every selected email.  This is the macro which decodes the email’s body and extracts the four values from it and copies them to the worksheet.  It also extracts the required properties from the email and copies them to the workbook.  I have documented all my assumptions about the email and the worksheet in the macro.  Make a copy of your workbook in case my assumptions are incorrect, and the macro damages the workbook.  However, I am hopeful that the macros will meet your requirement.

I hope I have explained the principles behind my code so you understand what is happening and can create your own macros to perform similar copying of data from emails to Excel.

```<code>Sub CtrlCopyEmailDataToExcel()

  ' Extracts data from selected emails and copies it to Excel

  ' Needs reference to Microsoft Excel n.nn Object Library
  ' where n.nn depends on the version of Office being used

  Dim Exp As Explorer
  Dim ItemCrnt As MailItem
  Dim Path As String
  Dim RowCrnt As Long
  Dim WbkEmailData As Excel.Workbook
  Dim WshtEmailData As Excel.Worksheet
  Dim XlApp As New Excel.Application

  Set Exp = Outlook.Application.ActiveExplorer

  If Exp.Selection.Count = 0 Then
    Call MsgBox("Please select one or more emails then try again", vbOKOnly)
    Exit Sub
  Else

    ' Replace with path to the folder which holds your
    Path = CreateObject("WScript.Shell").specialfolders("Desktop")

    With XlApp
      .Visible = True   ' Slows your application but makes debugging easier
      ' Replace "Email Data.xlsx" with the name of your workbook
      Set WbkEmailData = .Workbooks.Open(Path & "\Email Data.xlsx")
    End With

    With WbkEmailData
      ' Replace "Email Data" with the name of your worksheet
      Set WshtEmailData = .Worksheets("Email Data")
    End With

    With WshtEmailData
      ' If the cursor is placed in the bottom cell of column A and Up Arrow
      ' clicked, the cursor will be move up to the last row with data in
      ' column A.  This is the VBA equivalent.  Adding 1 means RowCrnt will
      ' become the number of the first unused row.  This relies on every
      ' used row having a value column A. Replace "A" if another column is
      ' a better choice.  If no column is guaranteed to not contain blank
      ' cell, I can provide a routine that finds the last used row in a
      ' different way.
      RowCrnt = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With

    For Each ItemCrnt In Exp.Selection
      Call CopyEmailDataToExcel(ItemCrnt, WshtEmailData, RowCrnt)
    Next
  End If

  WbkEmailData.Close SaveChanges:=True
  Set WshtEmailData = Nothing
  Set WbkEmailData = Nothing
  XlApp.Quit
  Set XlApp = Nothing

End Sub
Sub CopyEmailDataToExcel(ByRef ItemCrnt As MailItem, _
                         ByRef WshtEmailData As Worksheet, _
                         ByRef RowCrnt As Long)

  ' The Body of ItemCrnt is the HtmlBody with all the Html tags stripped out.

  ' Most the HtmlBody was a table with two cells per row.  The left-hand cell
  ' would be a keyword such as "Country", "Role" or "Product" while the
  ' right-hand cell would be the value associated with the keyword. The nature
  ' of the HtmBody and the nature of the conversion to text means a table row
  ' would become, for example: Country CR LF CR LF France CR LF CR LF

  ' The bottom of the table had only one cell per row. This is achieved with a
  ' "ColSpan = 2" attribute in the first and only <td> of the row.  The change
  ' of style starts with a cell containing "Message". These final rows are:
  ' Message CR LF CR LF Line1 CR LF CR LF Line2 CR LF CR LF ...
  ' The last two lines of this block are not required.

  ' The start of Body did not come from the same Html table and the format is
  ' slightly different.  However, the start of Body contains nothing of
  ' interest.

  ' Two properties of ItemCrnt are to be copied to the worksheet: SentOn (Date
  ' email sent) and SenderName.

  ' The month of property Sent On is required for another column.

  ' This routine copies the seven values from ItemCrnt to RowCrnt of the
  ' worksheet and then steps RowCrnt ready for the next row.

  Const ColReceived As Long = 1
  Const ColSentOn As Long = 2    ' Delivery Date
  Const ColMonth As Long = 3
  Const ColCountry As Long = 4
  Const ColEnqType As Long = 6   ' Role
  Const ColProduct As Long = 7
  Const ColMessage As Long = 8   ' Question

  Dim BodyLines() As String
  Dim InxL As Long
  Dim Key As String
  Dim LenKey As Long
  Dim Message As String
  Dim MessageFound As Boolean
  Dim Ub As Long

  ' At the start of ItemCrnt.Body, the lines are separated by single CRLFs.
  ' However, the start of ItemCrnt.Body contains nothing of interest so it
  ' does not matter that it is not split correctly.
  BodyLines = Split(ItemCrnt.Body, vbCrLf & vbCrLf)

  ' Ignore any trailing blank lines
  For Ub = UBound(BodyLines) To LBound(BodyLines) Step -1
    If BodyLines(Ub) <> "" Then
      Exit For
    End If
  Next

  With WshtEmailData

    MessageFound = False
    InxL = LBound(BodyLines)
    Do While InxL <= Ub

      If InStr(1, BodyLines(InxL), "Country") <> 0 Then
        ' The country is the next row
        InxL = InxL + 1
        .Cells(RowCrnt, ColCountry).Value = BodyLines(InxL)
      ElseIf InStr(1, BodyLines(InxL), "Role") <> 0 Then
        ' The role is the next row
        InxL = InxL + 1
        .Cells(RowCrnt, ColEnqType).Value = BodyLines(InxL)
      ElseIf InStr(1, BodyLines(InxL), "Product") <> 0 Then
        ' The product is the next row
        InxL = InxL + 1
        .Cells(RowCrnt, ColProduct).Value = BodyLines(InxL)
      ElseIf InStr(1, BodyLines(InxL), "Message") <> 0 Then
         ' The message starts in the next row
       InxL = InxL + 1
        MessageFound = True
        Exit Do
      End If

      InxL = InxL + 1
    Loop

    If MessageFound Then

      'Build message as Line1 vbLF Line2 vbLF Line3 and so on
      Message = BodyLines(InxL)
      For InxL = InxL + 1 To Ub
        If BodyLines(InxL) = "AAA-BBB-001366" Then
          Exit For
        End If
        Message = Message & vbLf & BodyLines(InxL)
      Next

      With .Cells(RowCrnt, ColMessage)
        .Value = Message
        .WrapText = True
      End With
    End If

    With .Cells(RowCrnt, ColReceived)
      .Value = ItemCrnt.ReceivedTime
      .NumberFormat = "dmmmyy"
    End With

    With .Cells(RowCrnt, ColSentOn)
      .Value = ItemCrnt.SentOn
      .NumberFormat = "dmmmyy"
    End With

    With .Cells(RowCrnt, ColMonth)
      .Value = ItemCrnt.ReceivedTime
      .NumberFormat = "mmm"
    End With

  End With

  RowCrnt = RowCrnt + 1

End Sub
</code>```

Body:
I receive a form in email that looks as follows.

The submitted details are below:

Region Europe
Country Spain
Contactable by email no
Contactable by phone no
Title MR
First name John
Last name Doe
Email j.doe@doe.com
Contact number 1234567
Role Customer
Institution companyname ltd
Product TEST product
Message
TEST Question

I need to extract some of the fields into the Excel Workbook.

I have been entering those emails manually so I need to find a code to extract data into next empty row.

My Excel headers look like this

Excel columns

I have checked some of the answers here and I managed to put together a code but it doesn't seem to work as needed.

Here what I have so far.

I entered it into Outlook VBA module.

Sub Extract()

    Dim myOlApp As Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim topOlFolder As Outlook.MAPIFolder
    Dim myOlFolder As Outlook.Folder
    Dim myOlMailItem As Outlook.MailItem

    Set myNameSpace = Outlook.Application.GetNamespace("mapi")
    Set objItem = objApp.ActiveExplorer.Selection.Item(1)

    Dim xlObj As worksheet
    Set xlObj = ActiveSheet

    Dim anchor As Range
    Set anchor = xlObj.Range("b2")

        anchor.Offset(0, 0).Value = "Country"
    anchor.Offset(0, 1).Value = "Role"
    anchor.Offset(0, 2).Value = "Product"
    anchor.Offset(0, 3).Value = "Message"
    anchor.Offset(0, 4).Value = "Sender"

    Dim msgText As String
    Dim msgLine() As String
    Dim messageArray() As String

    i = 0
    For Each myOlMailItem In myOlFolder.Items
        i = i + 1

        msgText = myOlMailItem.Body

        messageArray = Split(msgText, vbCrLf)

        For j = 0 To UBound(messageArray)

            msgLine = Split(messageArray(j) & ":", ":")

            Select Case Left(msgLine(0), 6)

                Case "Countr"
                    anchor.Offset(i, 0).Value = msgLine(1)

                Case "Role"
                    anchor.Offset(i, 1).Value = messageArray(j + 1)

                Case "Product"
                    anchor.Offset(i, 2).Value = messageArray(j + 1)

                Case "Message"
                    anchor.Offset(i, 3).Value = msgLine(1)

            End Select
            anchor.Offset(i, 4).Value = myOlMailItem.SenderName
            anchor.Offset(i, -1).Value = i

        Next
    Next
End Sub

I would really appreciate your help and comments.

Edit:

I have run the diagnostics tool and this is how email body looks like:

     |Message ‹2 crlf›|
      |TEST question - please confirm receipt if received. ‹2 crlf›|
      |AAA-BBB-001366 ‹2 crlf›|
      |JAN 2020 ‹2 crlf›|
      | ‹2 crlf›|
      |info.com <http://info.com/?mc_phishing_protection_id=xxxbpg|
      |db3lse2ip1c6bc0n0>‹2 s›‹2 crlf›|
      | ‹2 crlf›|
      | ‹3 crlf›|
      |This email (including any attachment) is intended only for the use by the recipients named above and|
      | contains proprietary information that may be confidential, copyrighted and/or privileged. Unauthori|
      |zed disclosure, use or copying is prohibited. If this email was sent to you in error or if you are n|
      |ot an intended recipient, please notify the sender immediately and delete this e-mail from your syst|
      |ems. Thank you‹crlf›|
Html: |‹2 crlf›|
      |<meta name="Generator" content="Microsoft Word 15 (filtered medium)">‹crlf›|
      |<title>Simple Transactional Email</title>‹crlf›|
      |‹crlf›|
      |‹crlf›|
      |

|
      |CAUTION:This email originated from outside of |
      |the Vifor Pharma Group organisation. Do not click on links or open attachments unless you recognise |
      |the sender and know the content is safe.
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |
<o:p> </o:p>
‹crlf›|
      |
<o:p> </o:p>
‹crlf›|
      |
<o:p> </o:p>
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |
From: Info GM  <noreply@info.com>‹crlf›|
      |
‹crlf›|
      |Sent: 19 February 2020 16:20
‹crlf›|
      |To: Info1 <pharma|
      |info2@info.com>
‹crlf›|
      |Cc: GM <GlobalMedInfo@info|
      |.com>
‹crlf›|
      |Subject: New query received<o:p></o:p>
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |
<o:p> </o:p>
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
 <|
      |o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |‹crlf›|
      |
‹crlf›|
      |
New query received‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
‹crlf›|
      |New product complaint query |
      |received from info.com<o:p></o:p>
‹crlf›|
      |
‹crlf›|
      |The submitted details are be|
      |low:<o:p></o:p>
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Region|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |‹crlf›|
      |
Europe|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Country|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |‹crlf›|
      |
Switzer|
      |land‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Title|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |‹crlf›|
      |
Ms|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
First n|
      |ame‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |‹crlf›|
      |
Joe|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Last na|
      |me‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |‹crlf›|
      |
Doe|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Email|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |‹crlf›|
      |
Joe.Doe.ext@info.com‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Contact|
      | number‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Role|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |‹crlf›|
      |
Other|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Institu|
      |tion‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Product|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |‹crlf›|
      |
TEST pr|
      |oduct‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
Message|
      |‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
TEST|
      | question - please confirm receipt if received.‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
ALL-ALL-001366‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|
      |‹crlf›|
      |
JAN 2020‹crlf›|
      |<o:p></o:p>
‹crlf›|
      |	‹crlf›|
      |
‹crlf›|
      |
‹crlf›|

I have run the tool and here it the text:

@Tony Dallimore Here is the text `Text: |From: Info <noreply@info.com <mailto:noreply@info.com> > ‹crlf›|
      |Sent: 19 February 2020 16:20‹crlf›|
      |To: Email1 <email1@info.com <mailto:email1|
      |@info.com> >‹crlf›|
      |Cc: Infor <Info1@infor.com <mailto:infor1@info.com> >‹crlf›|
      |Subject: New query received‹2 crlf›|
      | ‹2 crlf›|
      | ‹2 crlf›|
      |New query received ‹2 crlf›|
      |New product complaint query received from info.com‹2 crlf›|
      |The submitted details are below:‹2 crlf›|
      |Region ‹2 crlf›|
      |Europe ‹2 crlf›|
      |Country ‹2 crlf›|
      |Switzerland ‹2 crlf›|
      |Title ‹2 crlf›|
      |Mr ‹2 crlf›|
      |First name ‹2 crlf›|
      |Joe ‹2 crlf›|
      |Last name ‹2 crlf›|
      |Doe ‹2 crlf›|
      |Email ‹2 crlf›|
      |joedoe@info.com <mailto:joedoe@info.com>‹2 s›‹2 crlf›|
      |Contact number ‹2 crlf›|
      |‹tb›‹crlf›|
      |Role ‹2 crlf›|
      |Other ‹2 crlf›|
      |Institution ‹2 crlf›|
      |‹tb›‹crlf›|
      |Product ‹2 crlf›|
      |TEST product ‹2 crlf›|
      |Message ‹2 crlf›|
      |TEST question - please confirm receipt if received. ‹2 crlf›|
      |AAA-BBB-001366 ‹2 crlf›|
      |JAN 2020 ‹2 crlf›|
      | ‹2 crlf›|

Body:
I have added Option Explicit to the top of your code.  This is very helpful in finding compile-time errors.

Dim myOlApp As Outlook.Application
Set objItem = objApp.ActiveExplorer.Selection.Item(1)

You declare myOlApp but you use objApp without initialising it.

Dim myOlMailItem As Outlook.MailItem
Set objItem = myOlApp.ActiveExplorer.Selection.Item(1)

You declare myOlMailItem but you use objItem.

i = 0
For j = 0 To UBound(messageArray)

You do not declare i or j.  If you omit Option Explicit, you do not have to declare every variable because they will be declared for you as type Variant.  However, this means every minor spelling mistake becomes a new variable.  For example:

Count = Conut +1

Conut will be declared as a new variable and initialised to zero.

I do not get “User-defined type not defined”.  Thinking about this made me realise I had made a false assumption about where this code is placed.

You write, for example, Dim myNameSpace As Outlook.NameSpace and Dim anchor As Range.

You can write VBA within Outlook to access an Excel worksheet or you can write VBA within Excel to access an Outlook folder.  If you are within Outlook, you must tell the Outlook VBA compiler that you want to access Excel.  And vice versa.  You prefix NameSpace but you do not prefix Range.  I assumed this code was within an Excel workbook.  But on reflection, I now believe this code is within Outlook.  On rereading your question, I see that you say it is Outlook VBA; I missed that on my first reading.

It does not really matter where this code is placed; it cannot work.  If you are within Outlook, you cannot reference the active worksheet without opening an Excel workbook first.  If you are within Excel, which email has the user selected?

Let us step back from the code and consider your design options.

Using ActiveExplorer is my favourite method of testing a new email processing macro.  I can select a simple email for my first test.  I can correct the macro and rerun it against the same email as often as I wish.  Once the macro works on simple emails, I can try it on more complicated ones.  However, once my new macro is working properly, I want to automate the selection.  I do not want to think: “It is 12:00, have any of these emails arrived this morning?  If so, I need to go into to Outlook, find any that have arrived and process them.”  I want to think: “It is 12:00, have any new emails been copied to my Excel workbook.”

There are four distinct methods of selecting an email for processing.  You cannot mix and match.  These methods are:

User selection which is the method you are trying.

You can read down a folder (Oldest to Newest) or up a folder (Newest to Oldest) or you can sort the folder by any of the regular properties.

You can create a Rule which tells Outlook to monitor Inbox for particular emails and do something when one arrives.

You can use events to tell Outlook to call a macro whenever a new email is placed in a particular folder.

With method 1, it is the user that identifies an email to be processed.  With the other methods, there must be a test a macro can perform.  Are these emails all from the same sender?  Do these emails have a particular subject or a particular phrase in the subject?  Failing a simple check, you can look in the body for “Region”, “Country” and “Contactable by”.

A problem with methods 3 and 4, is that the macro is called when one of these emails arrive.  The macro must quickly open the workbook, update it and close it.  What is to happen if an email arrives while the workbook is being processed by a user?  I would not want to tackle this issue if I was new to VBA as you are.

I would use method 2.  I would have an Excel workbook that contained a macro that accessed Outlook’s Inbox and looked through the newest emails for any of the target emails.  This is not quite what I said I wanted but it is close enough.  Every time I open the workbook, I can, if I wish, run the macro to process any new emails.  This should only take a few seconds if the macro is run regularly.

It might be worth using a rule to move these emails to a dedicated folder.  This would mean that the macro does not have to search through Inbox for these emails.  This may look as though I am mixing methods 2 and 3 but not in the same macro.  Whenever, you open Outlook new emails arrive from the server and the rule will move any target emails to their folder.  When you open the workbook, you can decide to check for new target emails or review those emails already recorded in the workbook.

I think that is enough for you to think about for the moment.  Review the approaches I have discussed and come to some decisions about what you want.

Part 2

I have not had the free time I expected this evening so I doubt I will finish today.  I also have the problem that I should have asked more questions about your requirement.

To give you something to look at tomorrow, I have produced a demo macro which includes a lot of what you need to know and which I think you will find helpful in understanding the final macro.

I have created a workbook and worksheet with a heading that matches that shown in your question.  I have named the workbook “Email Data.xlsx” and have placed it on my desktop.  I have named the worksheet “Email Data”.  I assume you have different names and a different location.  The macro tells you what to change.

An Outlook macro that wants to access an Excel workbook, must have access to the Excel library.  You might know how to add references but just in case:

Open Outlook VBA Editor.

Click [Tools] in the Standard Toolbar then [References…].

A list of Available References is displayed.  Some available references at the top (such as Visual Basic for Application) are ticked.  Most references are not ticked.

If “Microsoft Excel nn.n Object Library” is ticked, click [Cancel] because there is nothing to do.  Note: the value of “nn.n” depends on the version of Outlook and Excel you are using.  This code should work with any version of Excel, so it does not matter if you are using an old version.

If “Microsoft Excel nn.n Object Library” is not ticked, scroll down the list until you find this reference and click the box n the left to tick it.

Click [OK].

Repeat step 2 and check that “Microsoft Excel nn.n Object Library” now appears at the top of the list and is ticked.

Copy this macro to an Outlook module:

Option Explicit
Sub DemoOpenWorkbook()

  ' Needs reference to Microsoft Excel n.nn Object Library
  ' where n.nn depends on the version of Office being used

  Dim Path As String
  Dim WbkEmailData As Excel.Workbook
  Dim WshtEmailData As Excel.Worksheet
  Dim XlApp As New Excel.Application

  ' Replace with path to the folder which holds your workbook
  Path = CreateObject("WScript.Shell").specialfolders("Desktop")

  With XlApp
    .Visible = True   ' Slows your application but makes debugging easier
    ' Replace "Email Data.xlsx" with the name of your workbook
    Set WbkEmailData = .Workbooks.Open(Path & "\Email Data.xlsx")
  End With

  With WbkEmailData
    ' Replace "Email Data" with the name of your worksheet
    Set WshtEmailData = .Worksheets("Email Data")
  End With

  With WshtEmailData
    Debug.Print .Cells(1, 1).Value
    Debug.Print .Cells(1, 2).Value
    Debug.Print .Cells(1, 3).Value
  End With

  WbkEmailData.Close
  Set WshtEmailData = Nothing
  Set WbkEmailData = Nothing
  XlApp.Quit
  Set XlApp = Nothing

End Sub

Make the changes I have discussed above.  Run the macro.  The workbook is opened, the first three headings from the worksheet are output to the Immediate Window and the Workbook is closed.  The Immediate Window will now contain:

DATE OF RECEIPT
DELIVERY DATE
MONTH

Work down my code.  If you do not understand any of my statements, look them up.  In general, typing something like “Outlook VBA Name-of-statement-you-do-not-understand” into your favourite search engine will find an explanation.  If necessary, come back with questions but I hope you can understand why this code works without any more help from me.

Note: you do not need to remember this code.  There are VBA statements and blocks of VBA code that I type often enough to remember them.  But I am not ashamed to look up something I use infrequently or to look back at a working macro that does something similar to what I want to do today.

Part3

The next two macros demonstrate my favourite technique for testing a new email processing macro.  Near the bottom of TestNewEmailProcessingMacro, you will find the statement Call EmailProcessingMacro(ItemCrnt).  When I create a new email processing macro, I amend this statement to call my new macro.  I then select a simple email of the type my macro processes before running TestNewEmailProcessingMacro().  I carefully check my macro processes the simple email correctly.  If not, I can correct the macro and rerun the test as often as necessary.  Once my macro processes a simple email correctly, I can try more complicated emails.  I continue until my macro performs to my entire satisfaction.  I would then call my new macro from a loop, a rule or an event as explained in the first part of my answer.

Sub TestNewEmailProcessingMacro()

  Dim Exp As Explorer
  Dim ItemCrnt As MailItem

  Set Exp = Outlook.Application.ActiveExplorer

  If Exp.Selection.Count = 0 Then
    Call MsgBox("Please select one or more emails then try again", vbOKOnly)
    Exit Sub
  Else
    For Each ItemCrnt In Exp.Selection
      If ItemCrnt.Class = olMail Then
        Call EmailProcessingMacro(ItemCrnt)
      End If
    Next
  End If

End Sub
Public Sub EmailProcessingMacro(ByRef ItemCrnt As MailItem)

  With ItemCrnt
    Debug.Print .ReceivedTime & " " & .Subject
  End With

End Sub

The above code shows how to use Explorer correctly.  In your code you have Set objItem = objApp.ActiveExplorer.Selection.Item(1).  This attempts to access the first selected email even if none are selected and ignores any further emails that have been selected.

I will next show you how to combine the techniques shown in the above macros.

Part 4

Below are the two macros which together do what I think you want.

You will need to make the same changes that were necessary with DemoOpenWorkbook().  That is, you will have to change the path, the name of the workbook and the name of the worksheet.  You will not need to add another reference to the Excel library, once is enough.

Select one or more of these emails and then run macro CtrlCopyEmailDataToExcel().  This is the macro which opens the workbook and then saves the changes.  This macro calls CopyEmailDataToExcel() for every selected email.  This is the macro which decodes the email’s body and extracts the four values from it and copies them to the worksheet.  It also extracts the required properties from the email and copies them to the workbook.  I have documented all my assumptions about the email and the worksheet in the macro.  Make a copy of your workbook in case my assumptions are incorrect, and the macro damages the workbook.  However, I am hopeful that the macros will meet your requirement.

I hope I have explained the principles behind my code so you understand what is happening and can create your own macros to perform similar copying of data from emails to Excel.

Sub CtrlCopyEmailDataToExcel()

  ' Extracts data from selected emails and copies it to Excel

  ' Needs reference to Microsoft Excel n.nn Object Library
  ' where n.nn depends on the version of Office being used

  Dim Exp As Explorer
  Dim ItemCrnt As MailItem
  Dim Path As String
  Dim RowCrnt As Long
  Dim WbkEmailData As Excel.Workbook
  Dim WshtEmailData As Excel.Worksheet
  Dim XlApp As New Excel.Application

  Set Exp = Outlook.Application.ActiveExplorer

  If Exp.Selection.Count = 0 Then
    Call MsgBox("Please select one or more emails then try again", vbOKOnly)
    Exit Sub
  Else

    ' Replace with path to the folder which holds your
    Path = CreateObject("WScript.Shell").specialfolders("Desktop")

    With XlApp
      .Visible = True   ' Slows your application but makes debugging easier
      ' Replace "Email Data.xlsx" with the name of your workbook
      Set WbkEmailData = .Workbooks.Open(Path & "\Email Data.xlsx")
    End With

    With WbkEmailData
      ' Replace "Email Data" with the name of your worksheet
      Set WshtEmailData = .Worksheets("Email Data")
    End With

    With WshtEmailData
      ' If the cursor is placed in the bottom cell of column A and Up Arrow
      ' clicked, the cursor will be move up to the last row with data in
      ' column A.  This is the VBA equivalent.  Adding 1 means RowCrnt will
      ' become the number of the first unused row.  This relies on every
      ' used row having a value column A. Replace "A" if another column is
      ' a better choice.  If no column is guaranteed to not contain blank
      ' cell, I can provide a routine that finds the last used row in a
      ' different way.
      RowCrnt = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With

    For Each ItemCrnt In Exp.Selection
      Call CopyEmailDataToExcel(ItemCrnt, WshtEmailData, RowCrnt)
    Next
  End If

  WbkEmailData.Close SaveChanges:=True
  Set WshtEmailData = Nothing
  Set WbkEmailData = Nothing
  XlApp.Quit
  Set XlApp = Nothing

End Sub
Sub CopyEmailDataToExcel(ByRef ItemCrnt As MailItem, _
                         ByRef WshtEmailData As Worksheet, _
                         ByRef RowCrnt As Long)

  ' The Body of ItemCrnt is the HtmlBody with all the Html tags stripped out.

  ' Most the HtmlBody was a table with two cells per row.  The left-hand cell
  ' would be a keyword such as "Country", "Role" or "Product" while the
  ' right-hand cell would be the value associated with the keyword. The nature
  ' of the HtmBody and the nature of the conversion to text means a table row
  ' would become, for example: Country CR LF CR LF France CR LF CR LF

  ' The bottom of the table had only one cell per row. This is achieved with a
  ' "ColSpan = 2" attribute in the first and only <td> of the row.  The change
  ' of style starts with a cell containing "Message". These final rows are:
  ' Message CR LF CR LF Line1 CR LF CR LF Line2 CR LF CR LF ...
  ' The last two lines of this block are not required.

  ' The start of Body did not come from the same Html table and the format is
  ' slightly different.  However, the start of Body contains nothing of
  ' interest.

  ' Two properties of ItemCrnt are to be copied to the worksheet: SentOn (Date
  ' email sent) and SenderName.

  ' The month of property Sent On is required for another column.

  ' This routine copies the seven values from ItemCrnt to RowCrnt of the
  ' worksheet and then steps RowCrnt ready for the next row.

  Const ColReceived As Long = 1
  Const ColSentOn As Long = 2    ' Delivery Date
  Const ColMonth As Long = 3
  Const ColCountry As Long = 4
  Const ColEnqType As Long = 6   ' Role
  Const ColProduct As Long = 7
  Const ColMessage As Long = 8   ' Question

  Dim BodyLines() As String
  Dim InxL As Long
  Dim Key As String
  Dim LenKey As Long
  Dim Message As String
  Dim MessageFound As Boolean
  Dim Ub As Long

  ' At the start of ItemCrnt.Body, the lines are separated by single CRLFs.
  ' However, the start of ItemCrnt.Body contains nothing of interest so it
  ' does not matter that it is not split correctly.
  BodyLines = Split(ItemCrnt.Body, vbCrLf & vbCrLf)

  ' Ignore any trailing blank lines
  For Ub = UBound(BodyLines) To LBound(BodyLines) Step -1
    If BodyLines(Ub) <> "" Then
      Exit For
    End If
  Next

  With WshtEmailData

    MessageFound = False
    InxL = LBound(BodyLines)
    Do While InxL <= Ub

      If InStr(1, BodyLines(InxL), "Country") <> 0 Then
        ' The country is the next row
        InxL = InxL + 1
        .Cells(RowCrnt, ColCountry).Value = BodyLines(InxL)
      ElseIf InStr(1, BodyLines(InxL), "Role") <> 0 Then
        ' The role is the next row
        InxL = InxL + 1
        .Cells(RowCrnt, ColEnqType).Value = BodyLines(InxL)
      ElseIf InStr(1, BodyLines(InxL), "Product") <> 0 Then
        ' The product is the next row
        InxL = InxL + 1
        .Cells(RowCrnt, ColProduct).Value = BodyLines(InxL)
      ElseIf InStr(1, BodyLines(InxL), "Message") <> 0 Then
         ' The message starts in the next row
       InxL = InxL + 1
        MessageFound = True
        Exit Do
      End If

      InxL = InxL + 1
    Loop

    If MessageFound Then

      'Build message as Line1 vbLF Line2 vbLF Line3 and so on
      Message = BodyLines(InxL)
      For InxL = InxL + 1 To Ub
        If BodyLines(InxL) = "AAA-BBB-001366" Then
          Exit For
        End If
        Message = Message & vbLf & BodyLines(InxL)
      Next

      With .Cells(RowCrnt, ColMessage)
        .Value = Message
        .WrapText = True
      End With
    End If

    With .Cells(RowCrnt, ColReceived)
      .Value = ItemCrnt.ReceivedTime
      .NumberFormat = "dmmmyy"
    End With

    With .Cells(RowCrnt, ColSentOn)
      .Value = ItemCrnt.SentOn
      .NumberFormat = "dmmmyy"
    End With

    With .Cells(RowCrnt, ColMonth)
      .Value = ItemCrnt.ReceivedTime
      .NumberFormat = "mmm"
    End With

  End With

  RowCrnt = RowCrnt + 1

End Sub